# Audit log

> Record events, and list, reveal and export a tenant's audit entries as NDJSON, CSV or OCSF, and purge them per tenant's retention.

Source: https://bettersupabase.com/docs/blocks/audit

The `audit` SQL module records row changes and `audit_event()` calls (see
[SQL modules](/docs/blocks/sql#audit-log)). The audit block reads that log
from TypeScript: a list for an activity page, an export for customers and
SIEMs, and retention.

```bash
better-supabase sql add audit
```

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

```ts title="better-supabase.config.ts"
export default defineConfig({
  sql: { modules: { audit: { options: { readPolicy: true } } } },
});
```

## Recording events [#recording-events]

`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:

```ts
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](/docs/auth/server#request-and-correlation-ids)
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](/docs/blocks/sql#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](/docs/blocks/sql#audit-log)). 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:

```ts
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:

```sql
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 actions [#module-actions]

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:

| Category        | Actions                                                      |
| --------------- | ------------------------------------------------------------ |
| `membership`    | invitations, member roles, removals, ownership, the waitlist |
| `access`        | roles, permissions and overrides in the access catalog       |
| `security`      | API keys, credentials, provider keys, support sessions, SSO  |
| `configuration` | organizations, settings, flags, announcements, workflows     |
| `billing`       | billing customers                                            |
| `data`          | row changes from the table trigger, attachments, exports     |
| `ai`            | agents, tool policies and approvals, chat shares             |
| `integration`   | connectors, incoming and outgoing webhooks                   |

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:

```ts title="better-supabase.config.ts"
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:

```ts
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 CloudEvents [#recording-cloudevents]

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

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

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

`auditEventOf(event)` is the mapping it uses:

| Audit field      | From the CloudEvent                                                    |
| ---------------- | ---------------------------------------------------------------------- |
| `eventType`      | `type` without the `dev.better-supabase.` prefix (`typePrefix` option) |
| `category`       | `security` for `account.*` and `support.*` (`category` option)         |
| `record`         | `subject`                                                              |
| `organizationId` | the `tenant` or `partitionkey` extension, then `data.organizationId`   |
| `actorId`        | `data.actorId`                                                         |
| `metadata`       | `data`                                                                 |
| `idempotencyKey` | `source` and `id`, so a redelivered event is recorded once             |

`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:

```ts
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](/docs/auth/account-deletion#recording-account-actions-in-the-audit-log)).

## Listing entries [#listing-entries]

`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](/docs/blocks/sql#calling-a-module-over-the-data-api).

```ts title="app/settings/audit/page.ts"
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`:

```ts
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`:

```ts
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](/docs/platform/list)
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.

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

## Exporting [#exporting]

`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:

```ts title="app/api/audit/export/route.ts"
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:

```ts
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](https://schema.ocsf.io/1.9.0) 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.

## Retention [#retention]

`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:

```ts title="jobs/purge-audit.ts"
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.