Audit log

The audit SQL module records row changes and audit_event() calls (see SQL modules). The audit block reads that log from TypeScript: a list for an activity page, an export for customers and SIEMs, and retention.

better-supabase sql add audit

Turn on readPolicy so members read their tenant's entries with audit.read, and platform staff read every entry:

export default defineConfig({
  sql: { modules: { audit: { options: { readPolicy: true } } } },
});

Recording eventsCopy Anchor Link

audit.record writes a semantic event through audit_event and returns the entry id. Call it with a service-role transport from a job, a webhook handler or an API route:

const id = await audit
  .record({
    eventType: "ticket.escalated",
    category: "support",
    organizationId,
    record: ticket.id,
    summary: "Escalated to the on-call team",
    requestId: ctx.requestId,
    correlationId: ctx.correlationId,
    metadata: { ticketId: ticket.id, priority: 2 },
  })
  .orThrow();

Its fields are the arguments of audit_event. actorId, actorKind, actorLabel, ip, userAgent, sessionId, requestId and scope count only for the service role and direct admin connections; any other caller gets its own user and the request's id. Pass ctx.requestId and ctx.correlationId from the server context so the event and the row changes of the same action share them; without them, audit_event reads the request's settings and headers (see Request and correlation ids). An id that isn't 1 to 128 safe characters is dropped. scope defaults to tenant with an organization and platform without one, and an adopted log can take its own value (region, say). An adopted log's columns outside the module's model are filled from metadata keys with sql.modules.audit.options.metadataColumns (see SQL modules). A key the event leaves out leaves its column to the column's default, so a not null default column keeps working, and the mapped keys are removed from the stored metadata unless keepMappedMetadata is true. list and export return those columns by name under columns; pass their types to createAuditLog to type them:

const audit = createAuditLog<{ ticket_id: string | null; priority: number }>({
  transport: sqlTransport(ctx.postgres),
});
const page = await audit.list({ organizationId }).orThrow();
// page.entries[0].columns: { ticket_id: "...", priority: 2 }

A column that follows from what the module already records, such as a flag for entries by platform staff, needs no metadata key: make it a generated column of the adopted log. Postgres fills it for every entry, from audit_event and from the row-change trigger alike, and the module never writes it:

alter table public.audit_logs
  drop column actor_is_platform_admin,
  add column actor_is_platform_admin boolean not null
    generated always as (coalesce(actor_kind = 'platform_admin', false)) stored;

With values: { actorKind: { support: "platform_admin" } }, a support session's entries and a service call with actorKind: "support" store platform_admin, and the flag follows. An expression that needs another table (a role lookup) can't be a generated column; pass that value as a metadata key instead.

Module actionsCopy Anchor Link

Every SQL module records its security-relevant actions in the log once the audit module is installed: a membership change, an API key, a setting, a flag, a credential, a connector, a webhook secret or a support session each write one entry, in the same transaction as the outbox event. The modules use one set of categories:

An adopted log whose category column has a check of its own maps the shared names with values, the same way it maps actor kinds:

audit: {
  options: {
    values: {
      category: { membership: "users", configuration: "settings" },
    },
  },
},

Set audit: false on a module to keep its actions out of the log; they still reach the outbox:

sql: { modules: { flags: { audit: false } } },

options.auditCategory on organizations, organizations-suspension and support-sessions still works and puts every entry of that module under one category, but it is deprecated: map the shared categories with values instead.

Recording CloudEventsCopy Anchor Link

audit.sink() returns an EventSink that records each CloudEvent it receives, so the events another part of the app already sends reach the log:

import { forwardBlockEvents } from "better-supabase/events";

forwardBlockEvents(betterSupabase, audit.sink(), {
  source: "/crm",
  types: ["organization.*", "support.*"],
});

auditEventOf(event) is the mapping it uses:

category takes the event type without its prefix and returns one of the module categories above, or undefined for none, so app events land next to the module actions:

audit.sink({
  category: (type) => (type.startsWith("invoice.") ? "billing" : undefined),
});

send throws when an entry fails, after trying the rest, so an outbox relay retries the batch. Build the log on a service-role transport: only the service role can set the actor. The server's audit option takes the same sink (see Account deletion).

Listing entriesCopy Anchor Link

createAuditLog reads the log through the module's own functions, as the caller, so the read policy decides what each member sees and an adopted log's column mappings are already applied. It needs no generated types and no exposed schema: pass sqlTransport(ctx.postgres) (direct Postgres as the user), or rpcTransport(supabase, { schema: "api" }) with the module's API schema.

import { createAuditLog, sqlTransport } from "better-supabase/blocks/audit";

const audit = createAuditLog({ transport: sqlTransport(ctx.postgres) });

const page = await audit
  .list({ organizationId, eventType: "invoice.sent", since, limit: 50 })
  .orThrow();
// page.entries: id, occurredAt, eventType, actorId, actorLabel, summary, targetLabel, ...
const older = await audit.list({ organizationId, cursor: page.next });

list calls list_audit_events with the tenant, event type, actor, target type, record, category, outcome, source, actor kind, correlation id and time filters, newest first; pass page.next as cursor for the next page. Every filter but the times takes one value or a list, search matches text in the event type, summary, target, actor and tenant labels, record and table (case-insensitive), order: "asc" lists oldest first, and count: true adds page.total, the number of entries the filters match, from count_audit_events:

const page = await audit
  .list({
    organizationId,
    eventType: ["invoice.sent", "invoice.paid"],
    outcome: "failure",
    search: "acme",
    order: "asc",
    count: true,
  })
  .orThrow();
// page.total: 12

A table with page numbers opens page N with offset next to count:

const page = await audit
  .list({
    organizationId,
    limit: 25,
    offset: (pageNumber - 1) * 25,
    count: true,
  })
  .orThrow();
// page.entries: the entries of that page; page.total: every match

offset skips that many entries after the filters (and after before, when both are set), and export takes it too, to start an export at that entry.

export takes the same filters, search and order. Restricted details (row snapshots, IP, user agent) are never in the list. audit.reveal(entryId) returns them for a member with the reveal permission in the entry's tenant, or platform staff, and records the look as an audit.revealed entry; anyone else gets not_found (AUDIT_ENTRY_NOT_FOUND).

auditListQuery is the older path: a list query over the audit table with facets for table, record, op, actor, event, category, outcome and target. It reads the table itself, so it needs the table in your generated models and follows the managed column names; use it with ctx.sql over direct Postgres, since the module schema is not exposed to the Data API.

export const auditList = auditListQuery(betterSupabase, "auditEvents");
const page = await auditList
  .run(ctx.sql, query.value, {
    where: { organizationId, ...auditList.between(from, to) },
  })
  .orThrow();

ExportingCopy Anchor Link

audit.export() streams every entry the caller can read as NDJSON, CSV or OCSF, one list_audit_events page at a time, so memory stays flat however long the log is, and it reads an adopted log and the version 3 columns (actorLabel, summary, requestId and the rest) like list does:

export const GET = bs.handler(async (request, ctx) => {
  const audit = createAuditLog({
    transport: sqlTransport(postgres.asUser(ctx.auth.claims)),
  });
  const stream = audit.export({ organizationId, since, format: "csv" });
  return new Response(stream, { headers: { "content-type": "text/csv" } });
});

format: "csv" writes a header row and one row per entry, with text that starts like a spreadsheet formula prefixed with '. columns picks and orders the columns (a record key, a key with its header label, columns.<name> for a column metadataColumns fills, or restricted.<field> for restricted details), preamble writes lines before the header row, and formatRow changes each row before it is written:

const stream = audit.export({
  organizationId,
  format: "csv",
  preamble: [`Audit log for ${organization.name}`, `Exported ${now}`],
  columns: [
    { key: "occurredAt", label: "Time" },
    { key: "actorLabel", label: "Who" },
    "eventType",
    { key: "columns.ticket_id", label: "Ticket" },
    { key: "restricted.ip", label: "IP address" },
  ],
  formatRow: (row, record) => ({
    ...row,
    occurredAt: record.occurredAt.toLocaleString("en-GB"),
  }),
});

A restricted.<field> column (ip, userAgent, sessionId, metadata, old, new or changes) reads each page's details through reveal_audit_entries(entries), which returns them for the entries the caller may reveal (the same check as reveal), leaves the cells of the others empty, and records one audit.revealed entry per tenant with the revealed ids in metadata.entries. It needs the restricted option and the access module. Without columns, the export keeps its default columns. For an export that runs in the background (a jobs handler that emails a link, say), audit.exportToStorage({ storage, bucket, path, signedUrlTtl, ...filters }) writes the file to Storage, such as the data lifecycle data-exports bucket, and returns its path and a signed download URL.

exportAuditLog(sql, options) streams NDJSON or OCSF straight from the managed table's columns. Keep it for a managed log; audit.export() covers adopted logs and CSV.

format: "ocsf" maps each entry to an OCSF event (SPEC_PINS.ocsf). Row changes are Entity Management (class_uid 3004) with the table and record as the entity; audit_event() entries are API Activity (6003) with the event type as api.operation. The activity comes from the event type's last segment: created is Create, exported is Read, renamed is Update and revoked is Delete; anything else is Other (99) with the event type as activity_name. toOcsf(entry, product) maps one entry.

RetentionCopy Anchor Link

purgeAuditLog deletes entries past their retention and returns how many. Without retention it calls purge_audit_log, which honours an audit_retention SQL hook; with it, each tenant is purged with its own number of days. setAuditRetention reads those days from a column:

import { purgeAuditLog, setAuditRetention } from "better-supabase/blocks/audit";

await purgeAuditLog(postgres.admin, {
  olderThan: "1 year",
  retention: setAuditRetention(postgres.admin, {
    table: "public.organizations",
    column: "audit_retention_days",
  }),
});

purgeAuditLog reads every tenant's days in one query. A tenant whose column is null keeps the olderThan default. Run the job until it returns 0; each call deletes at most batch entries per tenant.