The Audit Log Page

📝 摘要 #

本文是关于**产品审计日志（Product Audit Log）**设计的深度论述，提出审计日志有两个读者——构建者和审计员，而大多数日志是为前者设计的，本文教你如何为后者构建。文章围绕一个统一的 AuditEvent schema 和 43 条事件数据集展开五个论点，每个论点的字段设计都服务于最终审计查询的可用性。

核心概念： 审计日志的 schema 是系统中最持久的观点——一个 append-only 表上的列你可能四年后还在读，而遗漏的列意味着你永远无法追问那个问题。

关键技术栈 / 关键要点：

1. 事件模型（AuditEvent） — 17 个带注解字段，Actor 联合类型（user/api_key/system/agent/impersonation），每个字段都必须通过"能否合理地有另一种做法"测试
2. 事务性写入 — 审计事件与变更在同一事务中提交（one more INSERT, same COMMIT），保证要么都提交要么都不提交；当日志在事务无法触及的地方时退化为 outbox + processor 模式
3. 审计 vs 遥测边界 — 判断标准不是"有趣"而是"是否有人需要证明它发生过"；拒绝（denied）事件是高信号行，必须由授权层写入
4. 身份快照而非联表 — 用户身份复制进事件行而非运行时 JOIN，支持独立生命周期：账户删除不影响事件可读性，擦除请求精确 null 两个字段而不触碰其他
5. 秘密在写入时决定 — 凭证永远不进入 append-only 表（用 [redacted:write-time] 替代），因为追加日志使每次泄漏永久化
6. 擦除是一种窄授权 UPDATE — 只触及身份列、不删行、每次擦除本身写入审计事件；替代方案是用 per-subject 密钥加密身份字段后销毁密钥
7. Schema 即查询语言 — 通过结构化字段过滤器查询，无全文搜索；每个预置查询都是真实问题，只用 schema 已有的字段即可回答

关键结论 / 实践建议： 审计日志的核心设计原则是完整性不可后补——所有变更入口共享同一个服务层，事件在事务内写入，身份快照化，秘密写入时即剥离，schema 即是最终的查询语言。

Two readers01 Event model02 Write path03 What belongs04 Time & deletion05 Evidence

auditlog.dev

dark

An audit log has two readers. Most logs are designed for the wrong one. #

The builder writes it. The auditor reads it a year later, knows nothing about your codebase, and asks you to prove one claim about one week. Most logs are built for the debugging session on the afternoon they were written. This is what one looks like when it is built for the second reading.

This is about product audit logs — the record of what people and machines did inside your application. Not CloudTrail, not pgAudit, not syslog. The infrastructure literature covers those well and this badly.

One schema, one dataset of 43 events, five sections. The fields declared in 01 are the fields that answer the auditor’s question in 05. Check the page against itself.

A schema is the most durable opinion in the system. Endpoints get rewritten, UIs get redesigned. A column on an append-only table is one you are still reading in four years, and a column you left out is a question you can never ask about the past.

Every field has to survive the same test: would a competent engineer plausibly do this the other way? If yes, the field is a decision and deserves its defense. If no, it is furniture.

type AuditEvent = {  :           string  :     number  : string  :        Actor  :       string  :       Target | null  : UserSnapshot | null  :      "allowed" | "denied"  : string | null  :      Changes | null  :     Record<string, Json>  :       Origin  :   string  :  string  :  string} type Actor =  | { type: "user";          : UserSnapshot }  | { type: "api_key";       snapshot: ApiKeySnapshot; issued_by: UserSnapshot }  | { type: "system";        name: string }  | { type: "agent";         name: string; on_behalf_of: UserSnapshot | null }  | { type: "impersonation"; operator: UserSnapshot; subject: UserSnapshot } type UserSnapshot = {  id:           string           // an erasure never reaches this one  :        string | null    // null after an erasure, never before  name:         string | null} type Changes = {  :       string[]                        // what moved  before:       Record<string, Json> | null  // null when it did not exist  after:        Record<string, Json> | null  // null when it no longer does}

17 fields carry an argument. workspace_id does not — it is plumbing, so it is not annotated. A field that cannot survive this treatment does not belong in the schema.

Completeness is not a property you can add later. A dashboard, a REST call, an MCP server, a Slack agent and a nightly cron are five doors into the same mutation. The only thing they share is the service that performs it. Emit the event anywhere else and you are maintaining a list of doors, which is wrong the week someone adds one.

So the event is written in the same transaction as the change it describes — not after it, and not by something watching the database. Usually that is one more INSERT next to the row you just changed. It becomes an outbox and a processor only when the log lives somewhere the transaction cannot reach. Either way: both commit, or neither does.

One more INSERT, the same COMMIT, the same database. The event cannot survive a mutation that rolled back and the mutation cannot commit without it — which is the whole guarantee, bought without a second table or a process to operate. occurred_at and recorded_at are the same instant here, and most rows in a real log look exactly like this.

Draw the line between audit and telemetry yourself, or volume draws it for you: a check running every thirty seconds buries every role change you ever recorded. The test is not whether an event is interesting. It is whether someone might have to prove it happened.

The missing half is the refusals. An API key attempting a delete it has no scope for is the highest-signal row in the table — and it exists only if the authorization layer writes an event when it says no.

candidate eventverdict

• A member's role changes from member to adminaudit
• An HTTP check ran against api.example.com and returned 200 in 84mstelemetry
• An API key was denied monitor.delete for want of a scopeaudit
• A user opened the billing settings pagetelemetry
• Someone exported the audit logaudit
• A monitor's check interval changed from 60s to 30saudit
• A monitor flipped from up to downcontested It is an observation about the world, not an action taken by anyone in your system. There is no actor, so half the schema arrives empty — which is the tell. Keep it in incidents where it belongs, and log the human response (acknowledged, resolved) in the audit log instead. Some teams log both, and pay for it in a table where you cannot see the role change for the state changes.
• A session token was refreshedcontested
• A user switched the dashboard to dark modetelemetry
• A webhook delivery to Slack failed after three retriescontested
• A member was removed from the workspaceaudit
• A support engineer started an impersonation sessionaudit

Three of the twelve are marked contested, which is the honest count. A list where every answer is obvious teaches nobody anything, and the arguments worth having about an audit log are at its edges.

An audit log has to outlive the things it points at. Users get deleted, monitors get removed, workspaces get renamed, and the rows still have to read correctly years later. So identity is snapshotted into the event rather than joined to at read time.

The copy is not a promise of permanence. It is a promise of a separate lifecycle: deleting the account never reaches it, and an erasure request nulls the two fields that name a person without touching another column of another event. That precision is what the copy bought. Secrets are a different problem, and they are decided at the write. An append-only log makes every leak permanent, so anything you would not want in there forever cannot go in there once.

{
  "fields": [
    "url",
    "severity_filter"
  ],
  "before": {
    "url": "https://hooks.slack.com/services/T024F/B08KM/[redacted:write-time]",
    "severity_filter": "all"
  },
  "after": {
    "url": "https://hooks.slack.com/services/T024F/B09PQ/[redacted:write-time]",
    "severity_filter": "error,critical"
  }
}

The credential never entered the table, so there is nothing to leak later. The event still records what changed and who changed it, which is the part anyone actually needs.

actor:tom@northwind.dev

0 events— the name is gone

actor:usr_01HQ8TOMW

6 events— what they did survives

affected_user:usr_01HQ8TOMW

4 events— what was done to them, too

What the erasure reached: email, name, origin.ip on 2 events. What it left: origin.ip on 4, all of them denied.

Nothing was deleted. The stable id stayed, so these are still recognisably one account’s events — the failed logins from an address nobody recognised, and the attempt made the day after the account was removed. The email, the name, and the address Tom’s own sessions came from are gone. That trade is available only to a log that copied the identity in rather than pointing at it: there was something here to null.

Two things that word is glossing over. usr_01HQ8TOMW is a pseudonym, not an anonym — it points at a person for as long as anything anywhere maps it back to one, and this log is only one of the places that mapping lives. And an address is an identifier too, which is why the erasure clears origin.ip; it stops at the 4 denials, where the address is evidence of somebody else failing into the account.

Which raises the obvious objection: an append-only table you are allowed to UPDATE is not append-only. The resolution is that erasure is not an exception to the rule but a second, much narrower grant — one that can reach the identity columns of a row and nothing else, can never delete a row, and whose every use writes an audit event of its own. There is a member.data_erased row in this table for exactly that reason.

And if you would rather never issue that UPDATE at all: encrypt the identity fields under a per-subject key and destroy the key instead. The ciphertext stays, the row is never touched, and the name is just as unrecoverable.

Nobody reads an audit log. They interrogate one. The request arrives as a sentence — “show me who was granted admin in March” — and the only question is whether that sentence becomes a filter without anyone opening a database console.

So it is queried by structured filters, not free text. The schema is the query language. Every preset below is a real question, run live against the same 43 events, using only fields that section 01 already committed to.

4 of 43 events · action ∧ created

Answered by
affected_user, because the person whose role changed is not the person who changed it, and both have to be queryable. Note what comes back alongside the two admin grants: a role change that is not a promotion, and an attempt that was refused. The column names the field that moved; the row has the before and after. The filter is not narrowed to the flattering rows, because the schema does not know which rows are flattering.

The whole query language

actor:

Who acted — email, id, or API key name. Matches both identities of an impersonation.

actor_type:

user | api_key | system | agent | impersonation

action:

Exact (member.role_updated) or by category (member.*)

outcome:

allowed | denied

affected_user:

The other party — who the action was done to

target:

Resource type (monitor) or resource id (mon_01HQA1HLTH)

changed:

Which column moved — changed:role, changed:scopes. Reads changed_fields, not the diff.

country:

Two-letter country resolved from origin.ip at write time

request_id:

Everything that came from one gesture

created:

2026-03 · 2026-03-12 · 2026-03-01..2026-03-31 · >=2026-03-12

There is no free-text search. Type a bare word and the log will tell you so — the schema is the query language, and a text box over an audit log is an admission that the schema was never finished.