Chapter 57Lesson 5

The append-only audit log

Build the append-only audit_logs table in Postgres, a row-level policy that forbids updates and deletes, and the logAudit writer that records privileged actions inside the work's transaction.

Three months from now a support ticket lands: “An admin revoked my access and nobody can tell me who did it.” Open your database and you can’t answer. Your member table knows who’s an admin right now; it keeps no record of who granted or revoked that role, when, or from which IP. Your app tables record the present, never the past.

That gap is what the audit log fills: a separate, append-only table whose only 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 treated as a black box. Because that call runs inside the same transaction as the work, the audit row and the action it describes commit or roll back together, never one without the other. This lesson opens the box: you’ll build the audit_logs table, a row-level policy that makes editing or deleting history physically impossible, and the logAudit writer those flows have been calling.

What belongs in an audit log

The hard part was never the schema; it’s the judgment of what to put in the log, and the columns only make sense once you have it.

So picture the audience the intro named: a regulator, an incident responder, an auditor running a SOC 2 review months from now, reconstructing a chain of human decisions. The test is one question: would someone like that ask about this later? It sorts every event into two piles.

Audit-worthy is the privileged, human-initiated, state-changing verb: a member added when an invite is accepted, a member removed, a role changed, ownership transferred, the org deleted, the billing plan changed, the customer list exported, a sensitive setting flipped. Each is a decision a person made that an auditor could question, and that your current-state tables don’t preserve.

Not audit-worthy is everything else, and everything else is enormous. Reads and page views: opening a dashboard is not an event an auditor would ask about (a few regulated industries do log reads, and you’ll know if you’re one). High-frequency, low-stakes writes: a comment posted, a checkbox toggled, a task dragged across a board. And system writes that already have their own ledger: Stripe webhooks, when you reach them, land in a processed_events table built for exactly that, so logging them here would record the same fact twice.

The failure mode juniors walk into is over-recording. “Let’s just audit everything” feels responsible and is the opposite: the table becomes a debug log, ten thousand rows of dashboard.viewed for every member.removed, and the row that matters is buried when the incident comes. Audit the verbs, not the rows: “the invoice was voided” is signal; “the invoice’s updated_at changed” is noise.

The mirror-image mistake is under-recording: logging only failures, the way you handle errors. But a failed action is what Sentry is for. The audit log wants the successful privileged action, the admin who actually removed someone, not the attempts that bounced.

Two boundaries are easy to blur. An audit log is not an activity feed. The feed (“Sara commented on your doc”) is a product feature, rendered in the UI and tuned for engagement; the audit log is a compliance record with different retention, access, and contents. They may describe the same events, but they aren’t the same table, and you don’t build one by querying the other.

An audit log is also not your application logs. Logs and Sentry are operational telemetry: ephemeral, sampled, rotated out after weeks, read by engineers chasing a bug. The audit log is a durable, tenant-scoped business record that has to survive for years. Treat one as the other and you discover, mid-incident, that the data you need aged out of log retention a month ago.

Now sort these, asking the auditor’s question of each: 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.

Write an audit row Privileged human decisions an auditor would ask about later

Don't audit Reads, low-stakes ticks, or events with their own ledger

An admin removed a member from the org

A user opened the dashboard

The owner changed the billing plan

A user toggled a task to done

Ownership of the org was transferred

A Stripe webhook recorded a payment

Someone exported the full customer database

A user edited their own display name

The schema: append-only by design

The schema’s defining feature is what it leaves out: no updated_at, no deleted_at. Most tables carry those to record that a row changed or was archived; here a row is written once and never edited or removed. Rows only accumulate.

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 last column is createdAt, with no updatedAt or deletedAt: the schema gives you nothing to mutate. That absence is the first layer of the append-only contract.

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 who did it. Nullable on purpose: null means the system acted, not a person. onDelete: 'set null' keeps the row when the user account is deleted, leaving a null actor, because an audit trail you can erase by deleting a user is no 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(),
    ),
  ],
);

Context for reconstructing the action later: IP and user-agent, captured at write time. actorIp is text rather than Postgres’s native inet: Drizzle has no inet builder, and validating the shape isn’t worth a custom type here.

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 operation arguments for this event. Typed loosely, since its shape varies per event.

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 run.

1 / 1

The action column holds a verb string like 'member.role-changed' or 'org.deleted'. The entity.verb-pasttense shape is namespaced so you can filter a whole entity’s events (member.*), greppable so you can find every call site, and past-tense because the row records something that already happened.

subjectType and subjectId name what the action was done to, such as 'member' and that member’s id. subjectId is text, not uuid: most subjects are UUIDs, but some aren’t (a setting key, an external Stripe id), and one text column holds them all.

Blocking UPDATE and DELETE with a second RLS policy

In the previous chapter 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, so one tenant can never read another’s trail. This lesson adds a second policy that closes off mutation.

The org-isolation policy controls which rows you can touch; the new one controls which operations are allowed at all. For the app role we want exactly two, INSERT and SELECT, never UPDATE or DELETE. A bug that issues UPDATE audit_logs SET … should change zero rows, not because we trust the code but because the database refuses.

You write that as two more pgPolicy blocks in the schema, the single source of truth.

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();

The only new shape is for: 'update' / for: 'delete' paired with using: sql\false`. A policy's USINGclause is a predicate Postgres evaluates per row to decide whether the row is a candidate for the operation. Make it a literalfalseand no row ever qualifies, soUPDATEandDELETEchange nothing.INSERTandSELECT` go unmentioned, so they stay governed by the org-isolation policy.

Append-only is now protected three independent ways, each strong enough alone.

Write attempt

Column shape no updated_at / deleted_at nothing to mutate

DB policy USING (false) Postgres refuses

App discipline only logAudit() inserts no code path asks

audit_logs

UPDATE DELETE

refused

never reaches the table

INSERT

allowed

row written

Both write attempts run left to right through the same three gates. UPDATE and DELETE are refused at every gate and never reach the table; INSERT passes all three and lands a row.

The first layer is the column shape: with no updated_at or deleted_at, the schema doesn’t model a mutation at all. The second is the database policy you just wrote: Postgres refuses UPDATE and DELETE to the app role whatever query arrives. The third is application discipline: no code path in /lib or /app issues an UPDATE or DELETE against audit_logs, since the only writer, logAudit, inserts. The database refuses, and the application never asks.

Exactly one sanctioned path gets past this: 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 exempt from the forced policies, never the app role your handlers use.

Writing the row: `logAudit(tx, event)`

The interesting part of the writer isn’t the body, it’s the signature.

export const logAudit = (tx: Transaction, event: AuditEvent): Promise<void>

The first parameter is a Transaction, the handle Drizzle gives you inside db.transaction(async (tx) => …), not the pooled db. The audit insert has to ride inside the mutation’s transaction so the row and the work commit or roll back together (the iff guarantee: the audit row exists if and only if the work did), and it has to run inside the set_config('app.org_id', …) scope withTenant opens, or the isolation policy’s WITH CHECK rejects the new row. So logAudit is only correct from inside a transaction, and typing the parameter as Transaction makes a wrong call a compile error: logAudit(db, …) on the bare pooled client won’t type-check. Same move as tenantDb and authedAction: make the wrong shape impossible, not just discouraged.

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 transaction handle, not db. Pass the bare pooled client and it won’t type-check, so the audit write can only 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,
  });
};

The caller passes only the event; logAudit derives the rest. It reads actor and org from the request (requireOrgUser() is cached, so no extra query) and IP and user-agent from the headers. So call sites need only { 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. No external IO: an audit write is pure DB work, 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. There’s no Result to branch on: commit, the row is there; roll back, so does the row.

1 / 1

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 },
  });
});

The delete and the insert share tx, so they share a fate. Org isolation rides along: withTenant set app.org_id, and the policy’s WITH CHECK pins the new row to it.

The tempting mistake looks reasonable: to make the action feel snappier, defer the audit write, returning the response and then writing the row in a background callback. Compare the two shapes:

Breaks the iff
Correct

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 transaction closes after the delete, and after() fires only once the response has gone out. Two problems: logAudit(db, …) passes the pooled client where a Transaction is required, so it won’t compile; and even if it did, the member is already gone, so a failed deferred write leaves the action looking successful while the record 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, committing or rolling back with it. logAudit(tx, …) type-checks because tx is a real Transaction.

Deferring work to speed up a response is a good instinct elsewhere. Here it’s wrong: anything that pulls the write out of the transaction, a background callback, a queue, a fire-and-forget, breaks the iff and leaves a record that’s only usually right.

What goes in the `payload`

The payload is the jsonb column carrying the details of what happened, and it’s easy to get wrong in both directions: dump the whole affected row, or record nothing an incident responder can use. The right shape depends on the event.

A state change, such as a role changed or a setting edited, takes a { before, after } diff of only the fields that changed, not the whole row. An action event, such as an export or a deletion, has no “before,” so the payload carries the operation’s arguments. Compare a right-sized payload against the dump-everything reflex:

Over-stuffed
Right-sized

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 duplicate the actor and org columns.

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 a responder reads at a glance. The actor and org live in their own columns, so the payload carries only what those columns can’t.

The catalog from last lesson follows this shape: member.role-changed carries { before, after }, member.removed carries { previousRole }, and org.ownership-transferred carries { from, to, demotedTo }, each holding exactly the fields that make the event legible later.

Your payloads will hold 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 readability that’s the whole point, and protects nothing: what guards this data is the access control on the table, the org-isolation policy and the closely-held read surface, never the opacity of the bytes. The audit log is meant to hold sensitive data. Protect the table, not the bytes inside it.

Logging events that have no human actor

Not every audit-worthy event has a human behind it. A scheduled job promotes a trial to a paid plan when it expires; a webhook from your payment provider creates a subscription. These are privileged, state-changing events an auditor would ask about, but no person clicked anything.

That’s why actorUserId is nullable, and why logAudit is the wrong tool to write the row: the helper derives the actor and org from requireOrgUser() and headers(), and a webhook or scheduled job has no session, so the call throws before inserting. 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 carrying the provenance, the subsystem that 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 records that the system made a privileged change, and the payload says which part and why.

Admin impersonation, when a support engineer clicks “log in as user,” is another case the model doesn’t handle on its own. logAudit reads the actor from the session, which during impersonation is the impersonated user, so record both the impersonator and the impersonated to keep “who did this” answerable.

Retention and the read surface

Two operational questions follow from “rows only accumulate.” You’ll build neither here, but know their shape.

Retention. An append-only table grows without bound, so it needs a deletion horizon past which old rows are trimmed. Retain for the longer of two windows: the regulatory horizon your industry imposes, or the product’s own defensible window. With no regulatory commitment, around two years is a reasonable year-1 default. A scheduled job enforces it, running as the owner role, the only role that can DELETE past the policy and the forced RLS. An audit log often outlives the data it describes, because it records the actions taken on that data: a later GDPR chapter has you delete a customer’s records on request, yet the row saying “this customer was deleted, by this admin, on this date” may need to survive them.

The read surface. Admins and above get a read-only “Activity log” in settings: a paginated Server Component, filterable by actor and action, scoped through withTenant so the org-isolation policy applies to reads too. It runs a Drizzle query against the (organizationId, createdAt desc) index you put on the table for exactly this. A separate, closely held export-for-legal path pulls rows for compliance requests and writes its own 'audit.exported' event, so reads of the audit log are themselves audited.

Who audits the audit log? In year-1 SaaS, nothing: an audit-of-the-audit table is infinite regress. The log’s trustworthiness comes from the append-only contract and a retention job reviewed in code review like any other privileged path.

One privileged action, end to end

The audit log is the record half of this chapter: enforcement decides who may act, the log remembers what they did. What makes the record trustworthy is the move you’ve now seen in full, the same one behind tenantDb, withTenant, and authedAction: the audit row rides in the same transaction as the work, written through a helper that won’t compile outside one. Scrub through one privileged action’s lifecycle and watch where the audit row lands.

authedAction body

ctx = { user, orgId, role, db } session, role + input already checked

open transaction one transaction

set_config('app.org_id', orgId, true) every query inside is now pinned to this org

the work

tx.delete(member) the member row is removed

the audit row

logAudit(tx, { action, subjectId, payload }) same tx · WITH CHECK pins org · INSERT permitted

COMMIT

COMMIT both rows land together, or neither does

member deleted + audit row written

after commit

revalidatePath(...) member list refreshes · audit row is durable

`authedAction` has already checked the session, the role, and the input. The body runs with a ready `ctx` of `{ user, orgId, role, db }`, so everything below is the work, not the gatekeeping.

authedAction body

ctx = { user, orgId, role, db } session, role + input already checked

open transaction one transaction

set_config('app.org_id', orgId, true) every query inside is now pinned to this org

the work

tx.delete(member) the member row is removed

the audit row

logAudit(tx, { action, subjectId, payload }) same tx · WITH CHECK pins org · INSERT permitted

COMMIT

COMMIT both rows land together, or neither does

member deleted + audit row written

after commit

revalidatePath(...) member list refreshes · audit row is durable

`withTenant(orgId, …)` opens a transaction and runs `set_config('app.org_id', orgId, true)`. Every query is now pinned to this org, and everything in the box commits or rolls back as one unit.

authedAction body

ctx = { user, orgId, role, db } session, role + input already checked

open transaction one transaction

set_config('app.org_id', orgId, true) every query inside is now pinned to this org

the work

tx.delete(member) the member row is removed

the audit row

logAudit(tx, { action, subjectId, payload }) same tx · WITH CHECK pins org · INSERT permitted

COMMIT

COMMIT both rows land together, or neither does

member deleted + audit row written

after commit

revalidatePath(...) member list refreshes · audit row is durable

The mutation runs on the transaction handle `tx`: the member row is deleted. This is the work the action exists to do.

authedAction body

ctx = { user, orgId, role, db } session, role + input already checked

open transaction one transaction

set_config('app.org_id', orgId, true) every query inside is now pinned to this org

the work

tx.delete(member) the member row is removed

the audit row

logAudit(tx, { action, subjectId, payload }) same tx · WITH CHECK pins org · INSERT permitted

COMMIT

COMMIT both rows land together, or neither does

member deleted + audit row written

after commit

revalidatePath(...) member list refreshes · audit row is durable

`logAudit(tx, { action, subjectId, payload })` inserts the audit row on the same `tx`. The org-isolation policy's `WITH CHECK` pins it to the org, and the append-only policy permits the INSERT.

authedAction body

ctx = { user, orgId, role, db } session, role + input already checked

open transaction one transaction

set_config('app.org_id', orgId, true) every query inside is now pinned to this org

the work

tx.delete(member) the member row is removed

the audit row

logAudit(tx, { action, subjectId, payload }) same tx · WITH CHECK pins org · INSERT permitted

COMMIT

COMMIT both rows land together, or neither does

member deleted + audit row written

after commit

revalidatePath(...) member list refreshes · audit row is durable

COMMIT: the delete and the audit row land together, or neither does. This is the iff guarantee: the audit row exists if and only if the member was actually removed.

authedAction body

ctx = { user, orgId, role, db } session, role + input already checked

open transaction one transaction

set_config('app.org_id', orgId, true) every query inside is now pinned to this org

the work

tx.delete(member) the member row is removed

the audit row

logAudit(tx, { action, subjectId, payload }) same tx · WITH CHECK pins org · INSERT permitted

COMMIT

COMMIT both rows land together, or neither does

member deleted + audit row written

after commit

revalidatePath(...) member list refreshes · audit row is durable

After commit, `revalidatePath` refreshes the member list. The audit row is now durable, written once inside that transaction and by design never to change.

Two checks before you move on.

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?

It’s fine — after() always runs to completion, so the audit row lands a moment later either way.

The row still gets written, but its createdAt will be off by however long the response took.

The member is already deleted before the deferred write runs, so if that write fails the action still reports success while no record of who removed the member ever exists.

A callback that runs after the response has no way to reach the database, so the insert can never happen.

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?

The tx: Transaction type on logAudit rejects the call before it runs.

The deny-update policy: with no row ever satisfying its predicate, the UPDATE matches nothing and changes zero rows.

The absence of an updated_at column means there’s no field for the query to write to.

None of them — once a statement reaches Postgres it executes; row-level rules only filter reads.

This query is already past the type system and past application discipline — it’s a real 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.