The append-only audit log
Build the append-only audit_logs table in Postgres, with row-level security that forbids updates and deletes and a logAudit writer that records privileged actions inside the same transaction as the work.
Three months from now, a support ticket lands: “An admin revoked my access and nobody can tell me who did it.” A week later a security reviewer asks a sharper version of the same question: “show me every role change in this org over the last 90 days, with who made each one and from which IP.” Open your database and you’ll find you can’t answer either. Your member table knows who’s an admin right now. It has no memory of who made them an admin, or who removed the person in the ticket, or when, or from where. Your app tables record the present. They were never built to record the past.
That gap is what the audit log fills. It’s a separate, append-only table whose entire job is to remember privileged actions, who did what to whom, when, and from where, long after the rows they touched have changed or vanished. Every member-management flow you wrote in the last lesson already ends its transaction with a call to logAudit(tx, { action, … }), a helper you’ve been treating as a black box. This lesson opens the box. By the end you’ll have the audit_logs table, a second row-level policy that makes it physically impossible to edit or delete history, and the logAudit writer those flows have been calling.
One idea holds the whole lesson together, so let’s state it up front: the audit row lives or dies with the transaction that did the work. If the member was removed, the audit row exists. If the transaction rolled back, neither happened. The record and the work are one atomic unit, never two. Keep that in mind; we’ll keep coming back to it.
What an audit log is for, and what it isn’t
Section titled “What an audit log is for, and what it isn’t”Before you write a single column, you need to know what this table is for, because the column shape only makes sense once you do. The hard part of an audit log was never the schema. It’s the judgment of what to put in it.
An audit log is a forensic instrument. Picture the audience: a regulator, an incident responder, an auditor running a SOC 2 review three months from now. They’re not debugging your code. They’re reconstructing a chain of human decisions. So the test for whether something belongs in the audit log is simple: would someone like that ask about it later?
That test sorts every event into two piles.
Audit-worthy is the privileged, human-initiated, state-changing verb. A member was added when an invite was accepted. A member was removed. A role was changed. Ownership was transferred. The org was deleted. The billing plan changed. The customer list was exported. A sensitive setting flipped. Each one is a decision a person made that an auditor could plausibly question, and that your current-state tables don’t preserve.
Not audit-worthy is everything else, and “everything else” is enormous. First, reads and page views: nobody opening a dashboard is a forensic event (in year-1 SaaS; some regulated industries do log reads, and you’ll know if you’re in one). Second, high-frequency, low-stakes writes: a comment posted, a checkbox toggled, a task dragged across a board. Third, system writes that already have their own ledger: when you reach Stripe webhooks in a later chapter, those events get recorded in a processed_events table built for exactly that, so you don’t also write them here, or you’ve recorded the same fact twice.
Here’s the failure mode juniors walk straight into: over-recording. “Let’s just audit everything” feels safe and responsible. It is the opposite. Audit everything and the table becomes a debug log: ten thousand rows of dashboard.viewed and task.updated for every one row that says member.removed. The signal drowns. When the incident finally happens and someone goes looking for the row that matters, they can’t find it. An audit log that records everything records nothing useful. The rule that prevents this is to audit the verbs, not the rows. “The invoice was voided” is signal. “The invoice’s updated_at changed” is noise, a database fact rather than a human decision.
The mirror-image mistake is under-recording: logging only failures, the way you’d think about errors. But a failed action is what your error tracker (Sentry, in a later chapter) is for. The audit log wants the successful privileged action, because that’s exactly what an auditor came to see: not the attempts that bounced, but the admin who successfully removed someone. Failures go to Sentry. Successes go here.
Two more boundaries are easy to blur, so they’re worth spelling out.
An audit log is not an activity feed. An activity feed is a product feature, like “Sara commented on your doc,” rendered in the UI to make the app feel alive. It’s tuned for engagement, kept short, and nobody’s compliance depends on it. An audit log is a compliance instrument with different retention, different access, and different contents. The two might describe some of the same events, but they are not the same table, and you don’t build one by querying the other.
An audit log is not your application logs. Logs and Sentry are operational telemetry: ephemeral, sampled, rotated out after a few weeks, read by engineers chasing a bug. The audit log is a durable, queryable, tenant-scoped business record that has to survive for years. Treating one as the other is how teams discover, during an incident, that the data they need aged out of log retention a month ago.
Sort these. For each event, the only question is the one the auditor asks: is this a privileged human decision someone will ask about later?
For each event, decide whether it earns a row in the audit log. Drag each item into the bucket it belongs to, then press Check.
The table shape: append-only by design
Section titled “The table shape: append-only by design”Now the schema, justified against the judgment above. Start with the property that makes this table unlike any other you’ve written: there is no updated_at, and no deleted_at. Almost every table you’ve built carries those columns, since they’re how a row records that it changed or was archived. Leaving them out here is deliberate, and it’s the first layer of the append-only contract. This table doesn’t model change. A row is written once and is true forever; it is never edited and never removed. Rows only ever accumulate.
Here’s the full table. Read it once top to bottom, then we’ll walk the columns that carry a decision. As with every table in this codebase, the snake-case mapping lives on the db client, so TypeScript reads organizationId while the SQL column is organization_id, and that holds for every column below.
export const auditLogs = pgTable( 'audit_logs', { id: uuid().primaryKey().$defaultFn(() => uuidv7()), organizationId: uuid() .notNull() .references(() => organization.id, { onDelete: 'cascade' }), actorUserId: uuid().references(() => user.id, { onDelete: 'set null' }), actorIp: text(), actorUserAgent: text(), action: text().notNull(), subjectType: text(), subjectId: text(), payload: jsonb().$type<Record<string, unknown>>(), createdAt: timestamp({ withTimezone: true }).notNull().defaultNow(), }, (t) => [ index('idx_audit_logs_org_created').on( t.organizationId, t.createdAt.desc(), ), index('idx_audit_logs_org_actor_created').on( t.organizationId, t.actorUserId, t.createdAt.desc(), ), ],);Look at what’s missing here. The last column is createdAt, and there’s no updatedAt and no deletedAt. The schema doesn’t even give you a column to mutate. That absence is the first layer of the append-only contract: a row is written once and is true forever.
export const auditLogs = pgTable( 'audit_logs', { id: uuid().primaryKey().$defaultFn(() => uuidv7()), organizationId: uuid() .notNull() .references(() => organization.id, { onDelete: 'cascade' }), actorUserId: uuid().references(() => user.id, { onDelete: 'set null' }), actorIp: text(), actorUserAgent: text(), action: text().notNull(), subjectType: text(), subjectId: text(), payload: jsonb().$type<Record<string, unknown>>(), createdAt: timestamp({ withTimezone: true }).notNull().defaultNow(), }, (t) => [ index('idx_audit_logs_org_created').on( t.organizationId, t.createdAt.desc(), ), index('idx_audit_logs_org_actor_created').on( t.organizationId, t.actorUserId, t.createdAt.desc(), ), ],);The actor, the person who did it. Nullable on purpose: null means the system acted rather than a person, which gets its own section later. And onDelete: 'set null' is a deliberate choice: if the user account is later deleted, their audit rows survive with a null actor. An audit trail you can erase by deleting a user is no audit trail at all.
export const auditLogs = pgTable( 'audit_logs', { id: uuid().primaryKey().$defaultFn(() => uuidv7()), organizationId: uuid() .notNull() .references(() => organization.id, { onDelete: 'cascade' }), actorUserId: uuid().references(() => user.id, { onDelete: 'set null' }), actorIp: text(), actorUserAgent: text(), action: text().notNull(), subjectType: text(), subjectId: text(), payload: jsonb().$type<Record<string, unknown>>(), createdAt: timestamp({ withTimezone: true }).notNull().defaultNow(), }, (t) => [ index('idx_audit_logs_org_created').on( t.organizationId, t.createdAt.desc(), ), index('idx_audit_logs_org_actor_created').on( t.organizationId, t.actorUserId, t.createdAt.desc(), ), ],);The forensic context: IP and user-agent, captured at write time. actorIp is text here. Postgres has a native inet type that validates the shape, but Drizzle has no first-class inet column builder, and reaching for a custom type would be noise in this table. text is the deliberate simplification.
export const auditLogs = pgTable( 'audit_logs', { id: uuid().primaryKey().$defaultFn(() => uuidv7()), organizationId: uuid() .notNull() .references(() => organization.id, { onDelete: 'cascade' }), actorUserId: uuid().references(() => user.id, { onDelete: 'set null' }), actorIp: text(), actorUserAgent: text(), action: text().notNull(), subjectType: text(), subjectId: text(), payload: jsonb().$type<Record<string, unknown>>(), createdAt: timestamp({ withTimezone: true }).notNull().defaultNow(), }, (t) => [ index('idx_audit_logs_org_created').on( t.organizationId, t.createdAt.desc(), ), index('idx_audit_logs_org_actor_created').on( t.organizationId, t.actorUserId, t.createdAt.desc(), ), ],);The jsonb payload: the diff or the operation arguments for this specific event. It’s typed loosely here, since its shape is per-event and gets its own section below.
export const auditLogs = pgTable( 'audit_logs', { id: uuid().primaryKey().$defaultFn(() => uuidv7()), organizationId: uuid() .notNull() .references(() => organization.id, { onDelete: 'cascade' }), actorUserId: uuid().references(() => user.id, { onDelete: 'set null' }), actorIp: text(), actorUserAgent: text(), action: text().notNull(), subjectType: text(), subjectId: text(), payload: jsonb().$type<Record<string, unknown>>(), createdAt: timestamp({ withTimezone: true }).notNull().defaultNow(), }, (t) => [ index('idx_audit_logs_org_created').on( t.organizationId, t.createdAt.desc(), ), index('idx_audit_logs_org_actor_created').on( t.organizationId, t.actorUserId, t.createdAt.desc(), ), ],);Two composite indexes, both leading with organizationId: one for the per-org timeline (the common read), one for the per-actor filter (“everything this admin did”). Audit logs are write-heavy and read-rare, so every index taxes the insert. Index only the queries you’ll actually run, and no more.
A couple of columns deserve a closer look than a one-liner.
The action column holds a verb string like 'member.role-changed' or 'org.deleted'. The entity.verb-pasttense shape isn’t arbitrary. It’s namespaced so you can filter a whole entity’s events (member.*), greppable so you can find every call site in the codebase, and past-tense because an audit row records something that already happened. Pick the convention once and every event in the catalog follows it.
subjectType and subjectId name the thing the action was done to, such as 'member' and that member’s id. Notice subjectId is text, not uuid. That’s on purpose: most subjects are UUIDs, but some aren’t (a setting key, an external Stripe id), and a single text column holds all of them without forcing every event into the UUID mold.
The append-only policy: the second half of the RLS story
Section titled “The append-only policy: the second half of the RLS story”In the previous chapter you already did real work on this table. When you set up multi-tenancy, you enabled RLS on audit_logs and wrote the org-isolation policy: the FOR ALL rule that pins every query to the caller’s org via set_config('app.org_id', …), so one tenant can never read another’s audit trail. That policy is still doing its job, and this lesson doesn’t touch it. What this lesson adds is a second policy, one that closes off mutation entirely.
The org-isolation policy controls which rows you can touch. The new policy controls which operations are allowed at all. We want exactly two: INSERT and SELECT. No UPDATE, no DELETE, ever, for the app role . Even a bug that issues UPDATE audit_logs SET … should affect zero rows, not because we trust the code, but because the database refuses.
You write that as two more pgPolicy blocks in the schema, with the same single-source-of-truth discipline as last chapter, never a hand-edited migration for the policy itself.
export const auditLogs = pgTable( 'audit_logs', { /* …columns from above… */ }, (t) => [ // …the two indexes from above… pgPolicy('audit_logs_no_update', { for: 'update', to: authenticatedRole, using: sql`false`, }), pgPolicy('audit_logs_no_delete', { for: 'delete', to: authenticatedRole, using: sql`false`, }), ],).enableRLS();This is the same enable-and-force checklist as the org-isolation policy from last chapter, with nothing new there. The only new shape is for: 'update' / for: 'delete' paired with using: sql\false“.
using: sql\false`reads strangely until you see what it means. A row-level policy'sUSINGclause is a predicate Postgres evaluates per row to decide whether that row is a candidate for the operation. Make the predicate a literalfalseand no row ever qualifies, so anUPDATEorDELETEmatches nothing and changes nothing, every time.INSERTandSELECT` aren’t mentioned in these two policies, so they stay governed by the org-isolation policy from before. The table accepts new rows, lets you read your org’s rows, and refuses to change or remove any of them.
Step back and look at what’s now protecting append-only. It isn’t one mechanism but three independent layers, each strong enough on its own, and each failing independently of the others.
no updated_at / deleted_at nothing to mutate USING (false) Postgres refuses only logAudit() inserts no code path asks The first layer is the column shape. There is no updated_at and no deleted_at, so the schema doesn’t even model a mutation: there’s nothing to update. The second is the database policy you just wrote: Postgres refuses UPDATE and DELETE to the app role no matter what query arrives. The third is application discipline: nowhere in /lib or /app does any code path issue an UPDATE or DELETE against audit_logs, since the only writer, logAudit, inserts. The whole thing fits in one line: the database refuses, and the application never asks.
There is exactly one sanctioned way past this, and it’s rare: a legal order to retract a specific row, or the scheduled retention job that trims old rows. Both run as the database’s owner role, the privileged role that isn’t subject to the forced policies and holds the grants to delete, never the app role your handlers use. You met the two-role, least-privilege shape last chapter (the separate owner connection string), and the full role-and-grant setup comes in a later security chapter. For now, just know the seam exists and which side of it these escape hatches live on.
Writing the row: logAudit(tx, event)
Section titled “Writing the row: logAudit(tx, event)”Now the writer the whole chapter has been calling. We’ll build it small. The interesting part isn’t the body but the signature, because the signature is the safety mechanism.
export const logAudit = (tx: Transaction, event: AuditEvent): Promise<void>Look at the first parameter. It’s a Transaction, the transaction handle Drizzle hands you inside db.transaction(async (tx) => …), not the pooled db. That is a deliberate, load-bearing choice, and here’s the reasoning.
The audit insert has to ride inside the mutation’s transaction. That’s the iff guarantee from the start of the lesson: the audit row and the work must commit together or roll back together, which can only happen if they share a transaction. The insert also has to run inside the set_config('app.org_id', …) scope that withTenant opens, or the org-isolation policy’s WITH CHECK will reject the new row. Both requirements point at the same place: logAudit is only ever correct when called from inside a transaction.
So we encode that requirement in the type. Because the first parameter is typed as Transaction, calling logAudit(db, …) on the bare pooled client is a compile error: db is not a Transaction, and TypeScript rejects it before the code ever runs. The wrong way to call this function doesn’t compile.
You’ve seen this move three times now in this chapter. tenantDb makes “query without a tenant scope” not compile. authedAction makes “a privileged action without a role check” not compile. And now logAudit(tx, …) makes “an audit write outside its transaction” not compile. It’s one philosophy, applied at three layers: make the wrong thing impossible, not merely discouraged. A code-review comment catches a mistake sometimes. A type error catches it every time, for every developer, forever. That through-line is the payoff of the whole chapter.
Here’s the writer.
import 'server-only';
import { headers } from 'next/headers';
import { auditLogs, type Transaction } from '@/db';import { requireOrgUser } from '@/lib/auth';
const USER_AGENT_MAX = 512;
export type AuditEvent = { action: string; subjectType?: string; subjectId?: string; payload?: Record<string, unknown>;};
export const logAudit = async ( tx: Transaction, event: AuditEvent,): Promise<void> => { const { user, orgId } = await requireOrgUser(); const headerList = await headers(); await tx.insert(auditLogs).values({ organizationId: orgId, actorUserId: user.id, actorIp: headerList.get('x-forwarded-for'), actorUserAgent: headerList.get('user-agent')?.slice(0, USER_AGENT_MAX), action: event.action, subjectType: event.subjectType, subjectId: event.subjectId, payload: event.payload, });};The first parameter is a transaction handle, not db. This is the compile-error guarantee: pass the bare pooled client and it won’t type-check, so the audit write can only ever happen inside a transaction.
import 'server-only';
import { headers } from 'next/headers';
import { auditLogs, type Transaction } from '@/db';import { requireOrgUser } from '@/lib/auth';
const USER_AGENT_MAX = 512;
export type AuditEvent = { action: string; subjectType?: string; subjectId?: string; payload?: Record<string, unknown>;};
export const logAudit = async ( tx: Transaction, event: AuditEvent,): Promise<void> => { const { user, orgId } = await requireOrgUser(); const headerList = await headers(); await tx.insert(auditLogs).values({ organizationId: orgId, actorUserId: user.id, actorIp: headerList.get('x-forwarded-for'), actorUserAgent: headerList.get('user-agent')?.slice(0, USER_AGENT_MAX), action: event.action, subjectType: event.subjectType, subjectId: event.subjectId, payload: event.payload, });};Where the actor and org context come from. The caller passes only the event; logAudit reads who acted and which org from the request itself (requireOrgUser() is cached, so no extra query) and the IP and user-agent from the headers. That’s why the call sites only need { action, subjectId?, payload? }.
import 'server-only';
import { headers } from 'next/headers';
import { auditLogs, type Transaction } from '@/db';import { requireOrgUser } from '@/lib/auth';
const USER_AGENT_MAX = 512;
export type AuditEvent = { action: string; subjectType?: string; subjectId?: string; payload?: Record<string, unknown>;};
export const logAudit = async ( tx: Transaction, event: AuditEvent,): Promise<void> => { const { user, orgId } = await requireOrgUser(); const headerList = await headers(); await tx.insert(auditLogs).values({ organizationId: orgId, actorUserId: user.id, actorIp: headerList.get('x-forwarded-for'), actorUserAgent: headerList.get('user-agent')?.slice(0, USER_AGENT_MAX), action: event.action, subjectType: event.subjectType, subjectId: event.subjectId, payload: event.payload, });};A single insert on the transaction handle. There’s no external IO: an audit write is pure DB work, which is exactly what’s allowed inside a transaction.
import 'server-only';
import { headers } from 'next/headers';
import { auditLogs, type Transaction } from '@/db';import { requireOrgUser } from '@/lib/auth';
const USER_AGENT_MAX = 512;
export type AuditEvent = { action: string; subjectType?: string; subjectId?: string; payload?: Record<string, unknown>;};
export const logAudit = async ( tx: Transaction, event: AuditEvent,): Promise<void> => { const { user, orgId } = await requireOrgUser(); const headerList = await headers(); await tx.insert(auditLogs).values({ organizationId: orgId, actorUserId: user.id, actorIp: headerList.get('x-forwarded-for'), actorUserAgent: headerList.get('user-agent')?.slice(0, USER_AGENT_MAX), action: event.action, subjectType: event.subjectType, subjectId: event.subjectId, payload: event.payload, });};Returns nothing. The audit write isn’t a Result you branch on. If its transaction commits, the row is there, and if the transaction rolls back, so does the row. There’s no separate success to handle.
One contract detail is worth making explicit, because it’s why the call sites from the last lesson already work. The caller passes only the event, { action, subjectId?, payload? }. It does not pass the actor or the org. logAudit derives those itself: requireOrgUser() (already cached for this request, so it’s free) gives the user id and org id, and a headers() read gives the IP and user-agent. That’s the deliberate split: the action body knows what happened, and logAudit knows who acted and from where. It keeps every call site to the small shape the previous lesson shipped.
Now put it together in the shape you already know. Inside an authedAction body, the mutation and the audit row sit in one withTenant block:
await withTenant(orgId, async (tx) => { await tx.delete(member).where(eq(member.id, memberId)); await logAudit(tx, { action: 'member.removed', subjectType: 'member', subjectId: memberId, payload: { previousRole }, });});That’s the whole pattern made concrete: one transaction holds the work plus one audit row. The delete and the audit insert share tx, so they share a fate. The org isolation rides along too, because withTenant set the app.org_id and the policy’s WITH CHECK pins the new row to it. Work and record, one atomic unit.
The mistake to internalize is the one that looks reasonable. You might be tempted to make the action feel snappier by deferring the audit write: return the response immediately, then write the row afterward in a background callback. Compare the two shapes:
await withTenant(orgId, async (tx) => { await tx.delete(member).where(eq(member.id, memberId));});after(async () => { await logAudit(db, { action: 'member.removed', subjectId: memberId });});The work commits, then the audit write runs separately, and that’s the trap. The transaction closes after the delete, and after() runs its callback only once the response has gone out. There are two problems, one of them fatal. First, logAudit(db, …) passes the pooled client where a Transaction is required, so it won’t even compile. Second, even if it did, the member is already gone, so if this deferred write ever fails the action looks fully successful while the trail of who did it is silently lost.
await withTenant(orgId, async (tx) => { await tx.delete(member).where(eq(member.id, memberId)); await logAudit(tx, { action: 'member.removed', subjectType: 'member', subjectId: memberId, payload: { previousRole }, });});The audit row rides inside the same transaction as the work. They commit together or roll back together, so the row exists if and only if the member was actually removed. logAudit(tx, …) type-checks because tx is a real Transaction, and the wrong shape from the other tab never reaches production.
The left tab is the canonical trap, and it’s worth naming why it’s so tempting: deferring work to make a response faster is a genuinely good instinct in most places. It’s wrong here specifically because the audit row’s whole value is that it’s inseparable from the work. Anything that pulls the write out of the transaction, whether a background callback, a queue, or a fire-and-forget, breaks the iff and quietly turns your forensic record into a record that’s only usually right. Usually-right is worthless to an auditor.
The payload: readable forensics, not a column dump
Section titled “The payload: readable forensics, not a column dump”The payload is the jsonb column that carries the details of what happened, and juniors get it wrong in both directions: some dump the entire affected row, some record nothing useful and leave the incident responder guessing. The right shape depends on the kind of event.
For a state change, such as a role changed or a setting edited, the payload is a { before, after } diff, and it records only the fields that actually changed, not the whole row. For an action event, such as an export or a deletion, there’s no “before,” so the payload carries the operation’s arguments. Compare a good payload against the dump-everything reflex:
await logAudit(tx, { action: 'member.role-changed', subjectType: 'member', subjectId: member.id, payload: { member: { ...member }, organization: { ...organization }, changedBy: { ...actor }, },});A dump, not a diff. Three whole rows of mostly-unchanged fields bury the one fact that matters, what the role became, and quietly bloat every audit row with data the actor and org columns already hold.
await logAudit(tx, { action: 'member.role-changed', subjectType: 'member', subjectId: member.id, payload: { before: 'member', after: 'admin' },});A diff of exactly what changed. Two values an incident responder can read at a glance and reconstruct the decision from. The actor and the org already live in their own columns, so the payload only carries what those columns can’t.
The guiding idea is that the payload is human-readable forensics: terse enough to scan during an incident, complete enough to reconstruct what happened, and nothing more. It’s not a database snapshot. The catalog you built last lesson already follows this without you thinking about it: member.role-changed carries { before, after }, member.removed carries { previousRole }, and org.ownership-transferred carries { from, to, demotedTo }. Each payload is exactly the fields that make that event legible later.
There’s one counter-intuition here that experienced engineers internalize and beginners almost always get backwards. Your payloads will contain personal data: emails, names, the before-and-after of a profile field. The instinct is to hash or redact it “to be safe.” Don’t. Obfuscating the payload destroys the forensic readability that’s the entire point, and it buys you nothing, because the thing protecting this data was never the opacity of the bytes. It’s access control on the table. The org-isolation policy and the closely-held read surface are what keep this data safe. The audit log is supposed to hold sensitive information; that’s what makes it useful. Protect the table, not the bytes inside it.
The system actor: when nobody clicked the button
Section titled “The system actor: when nobody clicked the button”Not every audit-worthy event has a human behind it. A scheduled job promotes a trial to a paid plan at the moment it expires. A webhook from your payment provider creates a subscription. These are real, privileged, state-changing events an auditor would absolutely ask about, but no person clicked anything. There’s no actor.
That’s exactly why actorUserId is nullable, but logAudit is the wrong tool to write that row. The helper derives the actor and org from requireOrgUser() and headers(), and a webhook or scheduled job has no session behind it, so the call would throw before it ever inserted. A system writer skips the helper and inserts on the transaction directly: an explicit actorUserId: null, an action that names the subsystem ('system.subscription-created'), and a payload that carries the provenance, meaning which subsystem acted and the external id that triggered it:
await tx.insert(auditLogs).values({ organizationId, actorUserId: null, action: 'system.subscription-created', subjectType: 'subscription', subjectId: subscription.id, payload: { source: 'stripe-webhook', eventId: evt.id },});A null actor is information, not a missing value. It says the system did this, and the payload says which part and why. Don’t skip the audit row just because there’s no user to attribute it to; the fact that a machine made a privileged change is itself worth recording. You’ll wire up the webhook-driven case in a later chapter. Note the distinction now, though: the processed_events table that chapter introduces is about idempotency, making sure you don’t process the same webhook twice, while the audit row is the business record of what that webhook did. One webhook may write to both, since they answer different questions.
One more actor case the model doesn’t handle on its own is admin impersonation, the support engineer who clicks “log in as user” to debug someone’s account.
Here logAudit is quietly wrong, because it reads the actor from the session, and during impersonation the session is the impersonated user. A naive setup attributes every action to the customer and loses the one fact an auditor needs: which human was really at the keyboard.
The fix isn’t to suppress the row. It’s to record both the impersonator and the impersonated on the same entry, so “who did this” stays answerable.
Building that support tool is out of scope here. You saw the impersonatedBy column named back in the sessions lesson, and the audit event itself (admin.tenant-data-viewed) is catalogued in the unit-16 security baseline, in the chapter on the audit log policy.
How long the rows live: retention and the read surface
Section titled “How long the rows live: retention and the read surface”Two operational questions follow from “rows only ever accumulate,” and you should know the shape of both answers even though you won’t build either here.
Retention. An append-only table grows without bound, so it needs a deletion horizon, a point past which old rows are trimmed. The senior framing is to retain for the longer of two windows: the regulatory horizon your industry imposes (HIPAA wants roughly six years; SOC 2 commonly expects about one) or the product’s own defensible window. With no specific regulatory commitment, a reasonable year-1 default is around two years, enforced by a scheduled deletion job. That job runs as the owner role, the only role that can DELETE past the policy and the forced RLS. Here’s the counter-intuition worth holding onto: audit logs often have a longer retention than the data they describe, because they record the actions taken on that data. When you reach data-retention and GDPR in a later chapter, you’ll delete a customer’s records on request, but the audit row that says “this customer was deleted, by this admin, on this date” may need to outlive them. The job exists and runs as the owner role; that’s all you need to know now.
The read surface. Admins and above get a read-only “Activity log” in settings: paginated, filterable by actor and action, scoped through withTenant so the org-isolation policy applies on reads too. It’s a routine Server Component running a Drizzle query against that (organizationId, createdAt desc) index you put on the table for exactly this. The separate export-for-legal-response path, which pulls rows out for a compliance request, is authenticated, closely held, and itself writes an audit event ('audit.exported'): you audit the reading of the audit log. The page is routine to build and out of scope here. What matters is that you designed the table’s indexes around how it’ll be read.
One more question always comes up, so let’s answer it once and move on: who audits the audit log? In year-1 SaaS, nothing does. There’s no audit-of-the-audit table, and you should resist building one, because that road is infinite regress. The audit log’s own trustworthiness comes from the append-only contract, the no-DELETE policy, the closely-held owner role, and a retention job that’s reviewed in code review like any other privileged path. Knowing where to stop is itself the senior judgment here.
Putting it together
Section titled “Putting it together”Step back and the audit log clicks into place as the missing half of this chapter. Everything before it (roles, authedAction, the member flows) was the enforcement half: deciding who’s allowed to do what. The audit log is the record half: remembering what they did. The single mechanism that makes the record trustworthy is the one you’ve now seen end to end: the audit row rides in the same transaction as the work, written through a helper that won’t compile outside one.
That’s the same idea as tenantDb, withTenant, and authedAction. Three structural guarantees, one philosophy: make the wrong call shape impossible rather than merely against the rules. Scrub through one privileged action’s full lifecycle and watch where the audit row lands.
ctx = { user, orgId, role, db } session, role + input already checked set_config('app.org_id', orgId, true) every query inside is now pinned to this org tx.delete(member) the member row is removed logAudit(tx, { action, subjectId, payload }) same tx · WITH CHECK pins org · INSERT permitted COMMIT both rows land together, or neither does revalidatePath(...) member list refreshes · audit row is durable ctx = { user, orgId, role, db } session, role + input already checked set_config('app.org_id', orgId, true) every query inside is now pinned to this org tx.delete(member) the member row is removed logAudit(tx, { action, subjectId, payload }) same tx · WITH CHECK pins org · INSERT permitted COMMIT both rows land together, or neither does revalidatePath(...) member list refreshes · audit row is durable ctx = { user, orgId, role, db } session, role + input already checked set_config('app.org_id', orgId, true) every query inside is now pinned to this org tx.delete(member) the member row is removed logAudit(tx, { action, subjectId, payload }) same tx · WITH CHECK pins org · INSERT permitted COMMIT both rows land together, or neither does revalidatePath(...) member list refreshes · audit row is durable ctx = { user, orgId, role, db } session, role + input already checked set_config('app.org_id', orgId, true) every query inside is now pinned to this org tx.delete(member) the member row is removed logAudit(tx, { action, subjectId, payload }) same tx · WITH CHECK pins org · INSERT permitted COMMIT both rows land together, or neither does revalidatePath(...) member list refreshes · audit row is durable ctx = { user, orgId, role, db } session, role + input already checked set_config('app.org_id', orgId, true) every query inside is now pinned to this org tx.delete(member) the member row is removed logAudit(tx, { action, subjectId, payload }) same tx · WITH CHECK pins org · INSERT permitted COMMIT both rows land together, or neither does revalidatePath(...) member list refreshes · audit row is durable ctx = { user, orgId, role, db } session, role + input already checked set_config('app.org_id', orgId, true) every query inside is now pinned to this org tx.delete(member) the member row is removed logAudit(tx, { action, subjectId, payload }) same tx · WITH CHECK pins org · INSERT permitted COMMIT both rows land together, or neither does revalidatePath(...) member list refreshes · audit row is durable Two quick checks before you go.
An authedAction deletes a member and lets that transaction commit. To shave a little off the response time, it then schedules the logAudit write in an after() callback that fires once the request has already replied. What actually goes wrong?
after() always runs to completion, so the audit row lands a moment later either way.createdAt will be off by however long the response took.Suppose a bug slips past every code review and a request handler — connected as the app role — actually fires UPDATE audit_logs SET payload = … at the database. Of the three append-only layers, which one is doing the work at runtime to keep the rows from changing?
tx: Transaction type on logAudit rejects the call before it runs.UPDATE matches nothing and changes zero rows.updated_at column means there’s no field for the query to write to.UPDATE arriving at Postgres. The deny-update policy is the only layer that acts at that moment: its USING predicate evaluates to false for every row, so the operation finds no candidate and touches nothing. The missing updated_at column and the no-mutation-in-app-code rule are the other two layers, but both are prevention — they stop the query from ever being written. The policy is the runtime backstop that catches the one that wasn’t.Going further
Section titled “Going further”A few references if you want to dig into the patterns behind this lesson.
The source for the USING (false) deny-UPDATE/DELETE pattern, with the full USING / WITH CHECK semantics.
The judgment from the first section — which privileged events earn a row, and the who/what/when each must carry.
The payload column: jsonb plus .$type<..>() for compile-time shape on an otherwise loose blob.
Why the application-level trail is the half teams miss, and what a clean append-only record actually needs.