Chapter 59Lesson 3

Append-only audit_logs with RLS

When a privileged action changes who can do what inside an org, the record of it is compliance data: an auditor or an incident postmortem assumes nobody can quietly rewrite that record after the fact, not even an engineer at a psql prompt. This lesson builds a table the database itself holds to that rule. By the end, audit_logs can only grow: an insert succeeds inside a tenant-scoped transaction and is refused outside one, while UPDATE and DELETE touch zero rows even when you run them by hand as the app’s database role.

The only visible change in the app is the inspector’s raw-helpers panel, which until now rendered a placeholder and now resolves a real auditLogs count for the active org (1 for Acme, from the single row the seed plants). You will confirm the append-only guarantee yourself from psql output in the verification step.

Your mission

The audit log is compliance data, so “append-only” should be the database’s guarantee, not a convention you hope every engineer upholds. Two structural mechanisms enforce it: Postgres Row-Level Security policies that deny every UPDATE and DELETE, and a transaction-bound writer signature that makes an off-transaction audit call fail to typecheck.

Define the auditLogs table with its full column set. The id primary key is a uuid defaulted with uuidv7(), but organizationId and actorUserId are text: Better Auth generates organization.id and user.id as base62 text, so a uuid foreign key pointing at them emits DDL Postgres rejects. actorIp is text too, since Drizzle has no inet builder. payload is jsonb defaulting to {}. Add two composite indexes, both leading with organizationId: one on (organizationId, createdAt desc) for the per-org tail, one on (organizationId, actorUserId, createdAt desc) for per-actor lookups.

Then call .enableRLS() and declare three policies. A permissive FOR ALL org-isolation policy scopes reads and inserts to one tenant by comparing organization_id to current_setting('app.org_id', true). Two restrictive policies, one for UPDATE and one for DELETE, use sql\false`so no row ever qualifies. A restrictive policy intersects with the permissive one, sofalse` blocks every update and delete regardless of what else is allowed.

Two predicate details are easy to get wrong. The true in current_setting('app.org_id', true) makes a missing setting return NULL rather than raise, so the policy fails closed instead of surfacing a 500. And neither side carries a ::uuid cast, because both organization_id and the session variable are text. Set that variable transaction-locally with set_config('app.org_id', $orgId, true), never a plain SET: on a pooled connection, a session-level SET outlives the request and leaks one tenant’s app.org_id into the next. So withTenant(orgId, fn) opens a transaction, runs set_config, and runs the work; every audit read and write flows through it.

The writer, logAudit(tx, event), takes a Transaction and offers no overload for the bare db, so a role change and its audit row commit or roll back together. The caller passes only the event; logAudit derives actor and org from requireOrgUser() and the request headers, so the call site can’t spoof them.

One fact shapes how you verify this. The owner and superuser roles bypass RLS by default, so migrations (owner) and the seed (superuser postgres, which is how it inserts a fixture row with no withTenant) are unaffected; the deny policies only bite the authenticated role the request handler runs as. This lesson ships only the table and withTenant; the tenantDb query facade in the same file is the next lesson’s work.

The auditLogs table exists in the migrated schema with its full column set and both composite indexes.

tested

As the authenticated role inside a transaction that has set app.org_id, an INSERT INTO audit_logs (...) succeeds.

tested

The same insert outside a transaction, with app.org_id unset, is refused.

tested

UPDATE audit_logs SET action = 'x' matches zero rows for the authenticated role (UPDATE 0; the data is untouched).

tested

DELETE FROM audit_logs WHERE id = ... matches zero rows for the authenticated role (DELETE 0).

tested

A SELECT with app.org_id unset returns 0 rows rather than erroring.

tested

logAudit(tx, event) inserts exactly one row, and does not typecheck when called with the bare db instead of a Transaction.

tested

The inspector’s raw-helpers panel resolves the auditLogs row count, read through withTenant, for the current org.

untested

Coding time

Build it against the brief and the tests first. The reference solution is collapsed on purpose: open it once you have something running, or when a specific piece stalls you.

Reference solution and walkthrough

The table and its policies

One pgTable declaration carries the column types, the indexes, and the three RLS policies.

export const auditLogs = pgTable(
  'audit_logs',
  {
    id: uuid()
      .primaryKey()
      .$defaultFn(() => uuidv7()),
    organizationId: text()
      .notNull()
      .references(() => organization.id, { onDelete: 'cascade' }),
    actorUserId: text().references(() => user.id, { onDelete: 'set null' }),
    actorIp: text(),
    actorUserAgent: text(),
    action: text().notNull(),
    subjectType: text().notNull(),
    subjectId: text().notNull(),
    payload: jsonb().$type<Record<string, unknown>>().notNull().default({}),
    createdAt: timestamp({ withTimezone: true }).defaultNow().notNull(),
  },
  (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(),
    ),
    pgPolicy('audit_logs_org_isolation', {
      as: 'permissive',
      for: 'all',
      to: authenticatedRole,
      using: sql`${t.organizationId} = current_setting('app.org_id', true)`,
      withCheck: sql`${t.organizationId} = current_setting('app.org_id', true)`,
    }),
    pgPolicy('audit_logs_no_update', {
      as: 'restrictive',
      for: 'update',
      to: authenticatedRole,
      using: sql`false`,
    }),
    pgPolicy('audit_logs_no_delete', {
      as: 'restrictive',
      for: 'delete',
      to: authenticatedRole,
      using: sql`false`,
    }),
  ],
).enableRLS();

id is a standalone primary key with no incoming foreign key, so it stays uuid, defaulted with uuidv7() for time-ordered, index-friendly keys.

export const auditLogs = pgTable(
  'audit_logs',
  {
    id: uuid()
      .primaryKey()
      .$defaultFn(() => uuidv7()),
    organizationId: text()
      .notNull()
      .references(() => organization.id, { onDelete: 'cascade' }),
    actorUserId: text().references(() => user.id, { onDelete: 'set null' }),
    actorIp: text(),
    actorUserAgent: text(),
    action: text().notNull(),
    subjectType: text().notNull(),
    subjectId: text().notNull(),
    payload: jsonb().$type<Record<string, unknown>>().notNull().default({}),
    createdAt: timestamp({ withTimezone: true }).defaultNow().notNull(),
  },
  (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(),
    ),
    pgPolicy('audit_logs_org_isolation', {
      as: 'permissive',
      for: 'all',
      to: authenticatedRole,
      using: sql`${t.organizationId} = current_setting('app.org_id', true)`,
      withCheck: sql`${t.organizationId} = current_setting('app.org_id', true)`,
    }),
    pgPolicy('audit_logs_no_update', {
      as: 'restrictive',
      for: 'update',
      to: authenticatedRole,
      using: sql`false`,
    }),
    pgPolicy('audit_logs_no_delete', {
      as: 'restrictive',
      for: 'delete',
      to: authenticatedRole,
      using: sql`false`,
    }),
  ],
).enableRLS();

organizationId and actorUserId are text because Better Auth’s organization.id and user.id are base62 text; a uuid foreign key would emit invalid DDL. The org FK cascades; the actor FK is set null so deleting a user keeps the record of what they did.

export const auditLogs = pgTable(
  'audit_logs',
  {
    id: uuid()
      .primaryKey()
      .$defaultFn(() => uuidv7()),
    organizationId: text()
      .notNull()
      .references(() => organization.id, { onDelete: 'cascade' }),
    actorUserId: text().references(() => user.id, { onDelete: 'set null' }),
    actorIp: text(),
    actorUserAgent: text(),
    action: text().notNull(),
    subjectType: text().notNull(),
    subjectId: text().notNull(),
    payload: jsonb().$type<Record<string, unknown>>().notNull().default({}),
    createdAt: timestamp({ withTimezone: true }).defaultNow().notNull(),
  },
  (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(),
    ),
    pgPolicy('audit_logs_org_isolation', {
      as: 'permissive',
      for: 'all',
      to: authenticatedRole,
      using: sql`${t.organizationId} = current_setting('app.org_id', true)`,
      withCheck: sql`${t.organizationId} = current_setting('app.org_id', true)`,
    }),
    pgPolicy('audit_logs_no_update', {
      as: 'restrictive',
      for: 'update',
      to: authenticatedRole,
      using: sql`false`,
    }),
    pgPolicy('audit_logs_no_delete', {
      as: 'restrictive',
      for: 'delete',
      to: authenticatedRole,
      using: sql`false`,
    }),
  ],
).enableRLS();

actorIp is text: Drizzle has no first-class inet builder, and text is the deliberate simplification here.

export const auditLogs = pgTable(
  'audit_logs',
  {
    id: uuid()
      .primaryKey()
      .$defaultFn(() => uuidv7()),
    organizationId: text()
      .notNull()
      .references(() => organization.id, { onDelete: 'cascade' }),
    actorUserId: text().references(() => user.id, { onDelete: 'set null' }),
    actorIp: text(),
    actorUserAgent: text(),
    action: text().notNull(),
    subjectType: text().notNull(),
    subjectId: text().notNull(),
    payload: jsonb().$type<Record<string, unknown>>().notNull().default({}),
    createdAt: timestamp({ withTimezone: true }).defaultNow().notNull(),
  },
  (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(),
    ),
    pgPolicy('audit_logs_org_isolation', {
      as: 'permissive',
      for: 'all',
      to: authenticatedRole,
      using: sql`${t.organizationId} = current_setting('app.org_id', true)`,
      withCheck: sql`${t.organizationId} = current_setting('app.org_id', true)`,
    }),
    pgPolicy('audit_logs_no_update', {
      as: 'restrictive',
      for: 'update',
      to: authenticatedRole,
      using: sql`false`,
    }),
    pgPolicy('audit_logs_no_delete', {
      as: 'restrictive',
      for: 'delete',
      to: authenticatedRole,
      using: sql`false`,
    }),
  ],
).enableRLS();

payload is jsonb, typed Record<string, unknown>, defaulting to {} — where a role change stows its { before, after }.

export const auditLogs = pgTable(
  'audit_logs',
  {
    id: uuid()
      .primaryKey()
      .$defaultFn(() => uuidv7()),
    organizationId: text()
      .notNull()
      .references(() => organization.id, { onDelete: 'cascade' }),
    actorUserId: text().references(() => user.id, { onDelete: 'set null' }),
    actorIp: text(),
    actorUserAgent: text(),
    action: text().notNull(),
    subjectType: text().notNull(),
    subjectId: text().notNull(),
    payload: jsonb().$type<Record<string, unknown>>().notNull().default({}),
    createdAt: timestamp({ withTimezone: true }).defaultNow().notNull(),
  },
  (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(),
    ),
    pgPolicy('audit_logs_org_isolation', {
      as: 'permissive',
      for: 'all',
      to: authenticatedRole,
      using: sql`${t.organizationId} = current_setting('app.org_id', true)`,
      withCheck: sql`${t.organizationId} = current_setting('app.org_id', true)`,
    }),
    pgPolicy('audit_logs_no_update', {
      as: 'restrictive',
      for: 'update',
      to: authenticatedRole,
      using: sql`false`,
    }),
    pgPolicy('audit_logs_no_delete', {
      as: 'restrictive',
      for: 'delete',
      to: authenticatedRole,
      using: sql`false`,
    }),
  ],
).enableRLS();

Two composite indexes: the first serves the per-org audit tail, the second per-actor reads. Both lead with organizationId because every audit query is org-scoped.

export const auditLogs = pgTable(
  'audit_logs',
  {
    id: uuid()
      .primaryKey()
      .$defaultFn(() => uuidv7()),
    organizationId: text()
      .notNull()
      .references(() => organization.id, { onDelete: 'cascade' }),
    actorUserId: text().references(() => user.id, { onDelete: 'set null' }),
    actorIp: text(),
    actorUserAgent: text(),
    action: text().notNull(),
    subjectType: text().notNull(),
    subjectId: text().notNull(),
    payload: jsonb().$type<Record<string, unknown>>().notNull().default({}),
    createdAt: timestamp({ withTimezone: true }).defaultNow().notNull(),
  },
  (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(),
    ),
    pgPolicy('audit_logs_org_isolation', {
      as: 'permissive',
      for: 'all',
      to: authenticatedRole,
      using: sql`${t.organizationId} = current_setting('app.org_id', true)`,
      withCheck: sql`${t.organizationId} = current_setting('app.org_id', true)`,
    }),
    pgPolicy('audit_logs_no_update', {
      as: 'restrictive',
      for: 'update',
      to: authenticatedRole,
      using: sql`false`,
    }),
    pgPolicy('audit_logs_no_delete', {
      as: 'restrictive',
      for: 'delete',
      to: authenticatedRole,
      using: sql`false`,
    }),
  ],
).enableRLS();

The org-isolation policy is permissive and FOR ALL, so it governs both SELECT and INSERT. It compares organization_id directly to the session variable with no ::uuid cast (both sides are text), and the true flag makes a missing setting NULL, so the policy fails closed instead of erroring.

export const auditLogs = pgTable(
  'audit_logs',
  {
    id: uuid()
      .primaryKey()
      .$defaultFn(() => uuidv7()),
    organizationId: text()
      .notNull()
      .references(() => organization.id, { onDelete: 'cascade' }),
    actorUserId: text().references(() => user.id, { onDelete: 'set null' }),
    actorIp: text(),
    actorUserAgent: text(),
    action: text().notNull(),
    subjectType: text().notNull(),
    subjectId: text().notNull(),
    payload: jsonb().$type<Record<string, unknown>>().notNull().default({}),
    createdAt: timestamp({ withTimezone: true }).defaultNow().notNull(),
  },
  (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(),
    ),
    pgPolicy('audit_logs_org_isolation', {
      as: 'permissive',
      for: 'all',
      to: authenticatedRole,
      using: sql`${t.organizationId} = current_setting('app.org_id', true)`,
      withCheck: sql`${t.organizationId} = current_setting('app.org_id', true)`,
    }),
    pgPolicy('audit_logs_no_update', {
      as: 'restrictive',
      for: 'update',
      to: authenticatedRole,
      using: sql`false`,
    }),
    pgPolicy('audit_logs_no_delete', {
      as: 'restrictive',
      for: 'delete',
      to: authenticatedRole,
      using: sql`false`,
    }),
  ],
).enableRLS();

The deny layer: two restrictive policies, one for UPDATE and one for DELETE, each with a using predicate of false. A restrictive policy ANDs with the permissive one, so false qualifies no row — every update and delete matches zero rows.

1 / 1

The full file also carries the imports and the type exports the rest of the audit code consumes:

import { sql } from 'drizzle-orm';
import { authenticatedRole } from 'drizzle-orm/neon';
import {
  index,
  jsonb,
  pgPolicy,
  pgTable,
  text,
  timestamp,
  uuid,
} from 'drizzle-orm/pg-core';
import { uuidv7 } from 'uuidv7';

import { organization, user } from '@/db/schema/auth';

// ...auditLogs table (above)...

export type AuditLog = typeof auditLogs.$inferSelect;
export type NewAuditLog = typeof auditLogs.$inferInsert;

// The caller-supplied half of an audit row: the actor/org context is derived by
// logAudit from requireOrgUser + headers, so the event carries only the what.
export type AuditEvent = {
  action: string;
  subjectType?: string;
  subjectId?: string;
  payload?: Record<string, unknown>;
};

AuditEvent is the shape callers hand logAudit: only action is required, and the actor and org are derived, never passed in. This is the RLS-through-Drizzle pattern from chapter 056, applied to the live project.

One migration Drizzle does not write for you

pnpm db:generate reads .enableRLS() and emits ALTER TABLE "audit_logs" ENABLE ROW LEVEL SECURITY plus the three CREATE POLICY statements. That is necessary but not sufficient, and the gap silently undermines the append-only guarantee.

Two custom migrations fill it. The policies target TO authenticated, but that role does not exist on vanilla Docker Postgres — Neon and Supabase provision it, plain Postgres does not. A hand-written --custom migration creates the role idempotently, and it must run before the policy migration or the CREATE POLICY ... TO authenticated statements fail:

DO $$
BEGIN
  IF NOT EXISTS (SELECT FROM pg_roles WHERE rolname = 'authenticated') THEN
    CREATE ROLE authenticated NOLOGIN;
  END IF;
END
$$;

The second gap is the one to internalize. ENABLE ROW LEVEL SECURITY does not apply policies to the table owner, and migrations run as the owner. Without FORCE, a SET ROLE authenticated session owned by that same role bypasses every policy, and your deny-write guarantee becomes a no-op. FORCE ROW LEVEL SECURITY closes that. The same migration grants authenticated the schema and table privileges it needs, because on vanilla Postgres it has none; without the grants you hit “permission denied” before any policy is consulted, so you could never show that the policies are what refuse the write:

ALTER TABLE "audit_logs" FORCE ROW LEVEL SECURITY;
--> statement-breakpoint
GRANT USAGE ON SCHEMA "public" TO authenticated;
--> statement-breakpoint
GRANT SELECT, INSERT, UPDATE, DELETE ON "audit_logs" TO authenticated;

Spreading the audit schema into the client

For db.query.auditLogs to resolve and the Transaction type to exist, the audit module has to be merged into the Drizzle client: import it and spread it into the schema object alongside the others.

import { drizzle } from 'drizzle-orm/postgres-js';
import postgres from 'postgres';
import * as auditSchema from '@/db/audit';
import * as suppressionsSchema from '@/db/schema';
import * as authSchema from '@/db/schema/auth';
import { env } from '@/env';

// ...client...

export const db = drizzle(client, {
  schema: { ...suppressionsSchema, ...authSchema, ...auditSchema },
  casing: 'snake_case',
});

export const dbUnpooled = db;

// The transaction handle drizzle hands the db.transaction callback. withTenant and
// logAudit type their first arg as this so off-transaction audit writes don't
// typecheck (logAudit refuses a bare `db`).
export type Transaction = Parameters<Parameters<typeof db.transaction>[0]>[0];

The tenant-scoped transaction

withTenant is the only path through which audit reads and writes reach the database, because it is the only place that sets app.org_id. It opens a transaction, runs set_config('app.org_id', orgId, true) first, then runs your callback.

import 'server-only';

import { and, eq, type SQL, sql } from 'drizzle-orm';
import type { PgInsertValue, PgUpdateSetSource } from 'drizzle-orm/pg-core';

import type { Transaction } from '@/db';
import { db } from '@/db';
import { invitation, member } from '@/db/schema/auth';

// The audit-bearing transaction: set_config('app.org_id', orgId, true) is
// transaction-local (the SET LOCAL equivalent that takes a bind parameter) — never
// plain SET, which would leak the setting onto the pooled connection. The
// audit_logs org-isolation policy reads current_setting('app.org_id', true), so a
// tx without this would have its audit INSERT refused by the policy.
export const withTenant = async <T>(
  orgId: string,
  fn: (tx: Transaction) => Promise<T>,
): Promise<T> =>
  db.transaction(async (tx) => {
    await tx.execute(sql`select set_config('app.org_id', ${orgId}, true)`);
    return fn(tx);
  });

The true third argument scopes the setting to the transaction. That is the whole reason a transaction exists here: a session-level SET on a pooled connection would survive the request and leak app.org_id into the next request that borrows the same connection, silently crossing tenant boundaries.

The writer

logAudit inserts one audit row. Its signature is the discipline: tx: Transaction, with no overload accepting a bare db, so an audit write can only happen inside a transaction doing real work.

import 'server-only';

import { headers } from 'next/headers';

import type { Transaction } from '@/db';
import type { AuditEvent } from '@/db/audit';
import { auditLogs } from '@/db/audit';
import { requireOrgUser } from '@/lib/auth';

// The audit writer. Its first arg is the Transaction type with no bare-db overload,
// so an off-transaction call (and the role-changed-but-no-audit-row bug it would
// allow) does not typecheck. The caller passes only the event; actor/org context is
// derived here from requireOrgUser + the request headers, never trusted from input.
export const logAudit = async (
  tx: Transaction,
  event: AuditEvent,
): Promise<void> => {
  const { user, orgId } = await requireOrgUser();
  const h = await headers();

  await tx.insert(auditLogs).values({
    organizationId: orgId,
    actorUserId: user.id,
    actorIp: h.get('x-forwarded-for'),
    actorUserAgent: h.get('user-agent')?.slice(0, 512),
    action: event.action,
    subjectType: event.subjectType ?? '',
    subjectId: event.subjectId ?? '',
    payload: event.payload ?? {},
  });
};

The user-agent is sliced to 512 characters because a hostile or buggy client can send an arbitrarily long header, and an unbounded write into the audit table is a small denial-of-service vector.

Reading through the tenant

The two read helpers give the inspector something to render and let you prove the count filters correctly. Both route through withTenant for the same reason the writes do: the org-isolation policy compares against app.org_id, so without it set the policy matches no rows and returns zero.

import 'server-only';

import { desc } from 'drizzle-orm';

import { auditLogs } from '@/db/audit';
import { withTenant } from '@/db/tenant';

// Reads through withTenant so the org-isolation policy governs the count under a
// non-BYPASSRLS role (the predicate compares against the set app.org_id). Local dev
// connects as the superuser postgres, which bypasses RLS, so the policy is wired
// and demonstrable but not enforced on this path until a non-owner request role.
export const auditLogCount = async (orgId: string): Promise<number> =>
  withTenant(orgId, async (tx) => {
    const rows = await tx.select({ id: auditLogs.id }).from(auditLogs);
    return rows.length;
  });

// The audit tail the inspector renders: the org's most-recent events, newest first.
// Reads through withTenant for the same org-isolation reason as the count.
export const recentAuditLogs = async (orgId: string) =>
  withTenant(orgId, async (tx) =>
    tx
      .select({
        id: auditLogs.id,
        action: auditLogs.action,
        createdAt: auditLogs.createdAt,
      })
      .from(auditLogs)
      .orderBy(desc(auditLogs.createdAt))
      .limit(20),
  );

auditLogCount is the untested requirement on your checklist. Local dev connects as the superuser postgres, which bypasses RLS, so the policy is wired and demonstrable but not enforced on this read path until a request runs as authenticated — which is why the verification below has you SET ROLE authenticated by hand to see the policies bite.

Drizzle ORM — Row-Level Security

orm.drizzle.team

The pgPolicy/enableRLS API you write here, including permissive vs restrictive and the authenticatedRole helper.

Postgres — CREATE POLICY

postgresql.org

The reference for USING/WITH CHECK and the permissive-OR / restrictive-AND rule that makes your sql`false` deny policies bite.

Row Level Security for Tenants in Postgres

crunchydata.com

Crunchy Data on scoping tenant access with current_setting/set_config — the exact pattern withTenant implements.

Moment of truth

Run the lesson’s test suite:

pnpm test:lesson 3

It should pass. Green confirms the table shape, the RLS flag, and the three policies, plus the live behavior under the authenticated role: a tenant-scoped insert adds one row, an insert with app.org_id unset is refused, UPDATE and DELETE affect zero rows, and an unscoped SELECT returns nothing.

The deny policies apply only to the authenticated role, and your default connection is the superuser, which bypasses RLS. So confirm the rest by hand: open psql against the Docker Postgres and SET ROLE authenticated first.

SET ROLE authenticated;

-- inside a tenant transaction, the insert clears the org-isolation policy
BEGIN;
SELECT set_config('app.org_id', 'org_acme', true);
INSERT INTO audit_logs (id, organization_id, action, subject_type, subject_id)
VALUES (gen_random_uuid(), 'org_acme', 'manual.test', 'member', 'm_test');
-- INSERT 0 1
COMMIT;

-- with app.org_id unset, the same insert is refused
INSERT INTO audit_logs (id, organization_id, action, subject_type, subject_id)
VALUES (gen_random_uuid(), 'org_acme', 'manual.test', 'member', 'm_test');
-- ERROR:  new row violates row-level security policy for table "audit_logs"

-- the deny policies let no row qualify
UPDATE audit_logs SET action = 'x';
-- UPDATE 0
DELETE FROM audit_logs;
-- DELETE 0

-- a read with app.org_id unset returns nothing rather than erroring
SELECT * FROM audit_logs LIMIT 1;
-- (0 rows)

Inside BEGIN; SELECT set_config('app.org_id', 'org_acme', true); ...; COMMIT;, an INSERT INTO audit_logs (...) for org_acme succeeds.

untested

The same insert with app.org_id unset is refused with a row-level security violation.

untested

UPDATE audit_logs SET action = 'x' WHERE id = ... reports UPDATE 0 and leaves the data untouched.

untested

DELETE FROM audit_logs WHERE id = ... reports DELETE 0.

untested

SELECT * FROM audit_logs LIMIT 1 with app.org_id unset returns 0 rows.

untested

The inspector’s raw-helpers panel shows the auditLogs count for the current org (1 for Acme).

untested

No action writes to this table yet, so the audit tail in the inspector stays empty. The first writer caller arrives with the role-change action in the next lesson.