# SQL modules > Idempotent SQL modules for the database work every app repeats, written into your declarative schema. Source: https://bettersupabase.com/docs/blocks/sql `better-supabase sql add` writes SQL modules into `supabase/schemas`, and `supabase db schema declarative sync` turns them into a migration. The files are plain SQL that you can read and commit, so nothing runs behind your back. Every module can be re-run safely, and it keeps everything it creates in the `better_supabase` schema. `sql add` renders the new modules next to the ones `sql.modules` already lists, so a module sees what is installed (billing's foreign key to the organizations table, for example), and writes only the named modules and the ones they need. ```bash better-supabase sql list better-supabase sql add audit jobs pgtap supabase db schema declarative sync -f better_supabase_block better-supabase sql data ``` The extensions a module's schema file creates (`pgmq` for `jobs`, `pg_jsonschema` for `jsonb-schemas`, `vector` for `vector-search`) have to exist before the schema migration applies, because objects in it use them: a check constraint on `jsonb_matches_schema`, a pgmq queue. A schema diff can leave them out of its migration, so `sql add`, `sql sync` and `sql upgrade` write the ones no migration creates yet into `supabase/migrations/_better_supabase_extensions.sql`, stamped before the schema migration you create next. A migration you wrote yourself that creates the extension counts too, so the CLI writes nothing for it. `sql data` refuses to write the data migration while a module extension is missing from every earlier migration; run `sql sync`, then create the schema migration again after the extensions migration. A schema diff only captures objects, so each module's rows and settings (its `better_supabase.modules` row, the reserved slugs, the rate-limit role setting) go to a second file in `supabase/better-supabase-data/`, outside the schema folder: pg-delta loads every file under `supabase/schemas`, nested folders included, and refuses a table that has rows afterwards. When `sql.dir` is a folder inside the schema folder (`supabase/schemas/block`), the data files still go next to the schema folder, and `declarative_schema_path` in `supabase/config.toml` moves both. `better-supabase sql data` writes those files into one migration, stamped after the newest one so it runs after the schema migration. Its statements are idempotent, and it writes nothing when the latest data migration already has them. `sql add` and `sql sync` say when to run it. Event triggers belong to no schema, so a diff limited to some schemas (`supabase db schema declarative sync -s public,better_supabase`) leaves them out of its migration. Run the sync without `-s` when you use `audit` (`bs_audit_forget_dropped`) or `ensure-rls` (`bs_ensure_rls`). Their data files repeat the event triggers (`drop event trigger if exists`, then `create event trigger`), so the `sql data` migration creates them even when the schema migration lacks them, and [doctor BS323](/docs/cli/doctor#bs323) reports a module event trigger that the database, or every migration, lacks. The sync uses pg-delta, the Supabase CLI's diff engine (2.119 or later), which orders the schema files by dependency and carries grants, comments and `security_invoker` on views into the migration. It needs `[experimental.pgdelta] enabled = true` in `supabase/config.toml`: projects created by `supabase init` have it, and `better-supabase init` adds it to an existing `config.toml`. ### Legacy migra engine [#legacy-migra-engine] Without that table, the Supabase CLI diffs with migra, and [doctor](/docs/cli/doctor#bs316) reports BS316. `sql add` and doctor still name the migra command: `supabase db diff -f better_supabase_block`, run while the stack is stopped. When `supabase/config.toml` lists `[db.migrations] schema_paths`, migra reads only the files those entries match, so `sql add` names any module file no entry matches and prints the lines to add before the schemas that call their functions. Supabase's [switch guide](https://supabase.com/docs/guides/local-development/declarative-database-schemas#switching-to-pg-delta) lists the steps to move to pg-delta. ## Modules [#modules] | Module | What it adds | | -------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `updated-at` | `track_updated_at(table)` keeps an `updated_at` column current | | `actor` | `track_actor(table)` fills `created_by` and `updated_by` from `auth.uid()`; `impersonated_by => 'column'` also stamps an [impersonating](/docs/auth/impersonation) admin | | `audit` | `audit(table, ignore => '{updated_at}')` records inserts, updates and deletes in `audit_events`, keyed by the primary key, with the changed columns, the tenant column from `plugins.tenant.column` and any impersonating admin | | `tenant` | `memberships`, `current_tenant_id()`, `member_organization_ids(roles)` and `has_organization_role(organization, roles)` for RLS policies, and `membership_claims(user)` for the access token hook | | `access` | `can(scope, id, permission)`, `tenant_ids_with(permission)` and `is_platform(permission)` over roles, your own tables, an authorization provider or your functions. See [Access contract](/docs/blocks/access) | | `invitations` | `invite_member(tenant, email, role)` returns a single-use token, and only its hash is stored; `accept_invitation(token)` checks the signed-in user's confirmed email. See [Organizations](/docs/blocks/organizations) | | `reserved-slugs` | `track_slug(table)` rejects reserved words (`admin`, `api`, ...) and malformed slugs, also for `service_role` writes; `options.minLength` and `options.maxLength` (1 and 63 by default) bound the length, and `options.slugs` adds the app's own words, such as its route names, to the data file | | `jobs` | Jobs on Supabase Queues (pgmq) or a table: leases, retries with backoff, dead letters, dedupe keys and schedules (pg\_cron or the drain route). Used by [`better-supabase/blocks/jobs`](/docs/blocks/jobs) | | `idempotency` | Stores `Idempotency-Key` results so retried requests replay the first response | | `webhook-inbox` | Stores verified webhooks once and hands them to a worker | | `realtime-tables` | Payload-free change broadcasts for [live queries](/docs/frontend/live-queries), registered from `realtime.tables` | | `jsonb-schemas` | pg\_jsonschema `check` constraints for jsonb columns with a `schema` in the `json` config (see below) | | `pgtap` | Test helpers for `supabase test db`. See [Testing](/docs/testing) | | `grants` | The complete Data API privileges of the tables and functions in `expose`, for `anon`, `authenticated` and `service_role`, or grants derived from your policies with `options.fromPolicies`. See [Data API grants](/docs/guides/data-api-grants) | | `read-sets` | One `stable`, `security invoker` function per read set in `readSets`, written by `gen`. See [Read sets](/docs/repository/read-sets) | | `mfa` | `mfa_satisfied()` for restrictive policies that require `aal2` once a user has a verified factor. See [MFA and SSO](/docs/auth/mfa-sso) | | `sessions` | `session_active()` for restrictive policies that reject tokens whose session was signed out or expired, or whose user was banned or deleted; `options.policies` writes the policy on every table (`options.exclude` skips some), and `last_sign_ins(user_ids)` reads many users' last sign-in at once for the service role. See [Ended sessions](/docs/auth/account-deletion#ended-sessions) | | `entitlements` | `tenant_entitlements(tenant)`, `has_entitlement(tenant, key)` and `feature_claims(user)` over the Stripe Sync Engine. See [Entitlements](/docs/blocks/entitlements) | | `vector-search` | `search_(query, k)` per table in `vectorSearch`, for `db.$search`. See [Vector search](/docs/blocks/vector-search) | | `rate-limit` | `set_rate_limit(scope, max, period)` and `check_request()`, a `pgrst.db_pre_request` hook that answers writes over the limit with 429. See [below](#rate-limiting-writes) | | `support-sessions` | Support sessions: `start_support_session(target, reason)` checks `is_platform`, records the session and audits it, for the [support mode](/docs/auth/impersonation) | | `organizations` | `create_organization(attrs)`, member roles, ownership transfer, `switch_organization(organization)`, `list_my_organizations()`, `list_members(organization)` and an owner check on commit. See [Organizations](/docs/blocks/organizations) | | `profiles` | A profile per user from sign-up metadata, with a unique username, an email mirror, `my_profile()`, `update_my_profile(attrs)`, column grants and service-owned columns. See [Profiles](/docs/blocks/profiles) | | `outbox` | `emit_event(type, payload)` in the writing transaction, named consumers with their own cursor, backoff and dead letters, `track_events(table)` and a relay to any `EventSink`. Other modules emit to it once it's installed. See [Outbox](/docs/blocks/outbox) | | `notifications` | `notify(jsonb)` as a `security definer` sender, per-recipient read, dismissed and resolved state, subject subscriptions, channel preferences, email and push deliveries, and a private realtime topic. See [Notifications](/docs/blocks/notifications) | | `inbox` | Shared inboxes per organization: contacts with channel identities, conversations with assignment, teams, status, snoozing and bot handoff, messages and internal notes, read state, deliveries, a webhook event store, attachments in a private bucket and private realtime topics. See [Inbox](/docs/blocks/inbox) | | `chat-sdk-state` | The Chat SDK state tables: subscriptions, token-checked locks, a TTL cache, lists and per-thread queues, behind service-only functions, and `purge_chat_state`. See [Chat SDK state](/docs/chat-sdk/state) | | `webhooks-out` | Customer webhook destinations with event subscriptions, Vault secrets with rotation, a delivery log with leases, retries, redelivery and auto-disable, and `publish_webhook_event` as a service-only fan-out. See [Outgoing webhooks](/docs/blocks/webhooks-out) | | `webhooks-in` | Per-tenant trigger URLs with hashed tokens, optional Standard Webhooks or HMAC verification with secrets in Vault, a body limit, receive counters and a rate limit; deliveries go to the webhook inbox. See [Incoming webhooks](/docs/blocks/webhooks-in) | | `streams` | Durable streams: ordered chunks with idempotent appends, a cancel flag the writer reads on its next append, owner reads through RLS, a payload-free Realtime ping per stream and `purge_streams`. See [Durable streams](/docs/blocks/streams) | | `credentials` | `credential_get`, `credential_set` and `credential_delete` over Supabase Vault, for `service_role` only, behind `vaultCredentials()`. See [Credentials](/docs/extending/credentials) | | `ai-chat` | Chats, projects and a branching message tree in the canonical AI message format, runs with a compare-and-set stream claim and step progress, tool approvals and policies, pending inputs, cited sources, feedback, hashed share links, a model catalog per plan, moderation events, experimental harness sessions, the `ai_sandboxes` registry of chat and harness sandboxes with a service-only idle stop, and private Realtime topics. See [AI chat](/docs/blocks/ai-chat) | | `ai-files` | Files for AI chats in a private bucket whose policies only accept a reserved upload, provider file references with expiry, versioned documents with suggested edits, and a purge for stale uploads and the files of deleted chats. See [AI files](/docs/blocks/ai-files) | | `knowledge` | Documents and chunks for retrieval, each chunk with an embedding and a `tsvector`, scoped to an organization, agent, project, chat or user; hybrid search with reciprocal rank fusion, chunks that keep their embedding when unchanged, and an embed job per document when `jobs` is installed. See [Knowledge](/docs/blocks/knowledge) | | `memory` | Core memory files under `/memories` edited with the memory tool commands, archival facts with embeddings, one embedding per chat message for recall, namespaces per user, agent, chat or organization, and versioned documents for agent runtimes. See [Memory](/docs/blocks/memory) | | `agents` | Saved assistants with instructions, a model, tools, connectors, knowledge scopes and starters; private, organization or public visibility, installs, ratings and skill references. See [Agents](/docs/blocks/agents) | | `connectors` | MCP servers per organization, one OAuth grant per user behind a `credential_ref` that is revoked with the grant, MCP sessions per chat, and tool list fingerprints an admin approves. See [Connectors](/docs/blocks/connectors) | | `ai-tasks` | Prompts scheduled on a cron in a time zone, a service-only tick that queues due runs to the `ai_task_run` queue, and a run log. See [AI tasks](/docs/blocks/ai-tasks) | | `push` | `push_devices` per user with RLS, `register_push_device` and `unregister_push_device` for the user, and `push_tokens_for` and `prune_push_tokens` for `service_role`. See [Push notifications](/docs/blocks/push) | | `ai-cache` | Model responses cached under a key the app derives from the request, with a TTL capped by `maxTtl`, an entry size cap (`maxBytes`), hit counts and a purge of expired entries; only the service role reads and writes it. See [AI cache](/docs/blocks/ai-cache) | | `ai-providers` | Per-tenant provider keys stored as `credential_ref`s, and the `ai_batches` registry of provider batch jobs with their stored results. See [AI providers](/docs/blocks/ai-providers) | | `api-keys` | `api_keys` with only the SHA-256 of each secret, `create_api_key`, `rotate_api_key`, `revoke_api_key`, `verify_api_key(public_id, secret_hash)` for the server and `has_scope(scope)` for policies. See [API keys](/docs/blocks/api-keys) | | `settings` | Per-user and per-organization settings with defaults, `get_user_settings()`, `set_organization_setting(tenant, key, value)` and the reset functions. See [Settings](/docs/blocks/settings) | | `usage` | Usage counters per tenant, meter and period, `record_usage` with idempotency keys, quotas per tenant or plan, and `within_quota(tenant, meter)` and `consume_quota` for RLS and RPCs. See [Usage and quotas](/docs/blocks/usage) | | `billing` | Stripe customers per tenant, `billing_status(tenant)`, `billing_seat_count(tenant)` and `link_billing_customer`. See [Billing](/docs/blocks/billing) | | `flags` | Flags with targeting rules, overrides and percentage rollouts, and `flag_enabled(key)` and `tenant_ids_with_flag(key)` for policies, bucketed the same way as the TypeScript provider. See [Feature flags](/docs/blocks/flags) | | `comments` | Threaded comments on any record with mentions, `create_comment`, `list_comments`, and an activity feed built from outbox events. See [Comments and activity](/docs/blocks/comments) | | `attachments` | Attachment records linked to Storage objects, tenant-scoped Storage policies, and a scan status that gates downloads. See [Attachments](/docs/blocks/attachments) | | `data-lifecycle` | Data exports per user or organization, `request_organization_deletion(tenant)` with a grace period, `cancel_organization_deletion` and `purge_organization`. See [Data lifecycle](/docs/blocks/data-lifecycle) | | `sso` | Verified organization domains with auto-join, SAML providers per organization, SSO enforcement in the access token hook and the SCIM user and group functions. See [SSO and SCIM](/docs/blocks/sso) | | `onboarding` | `onboarding_progress` per user or organization, `complete_onboarding_step(checklist, step, tenant)` and a trigger that completes steps from outbox events. See [Onboarding](/docs/blocks/onboarding) | | `waitlist` | `join_waitlist(email)`, approvals, hashed invite codes with use limits and an optional organization, and a trigger that redeems the code on sign-up. See [Waitlist and invite codes](/docs/blocks/waitlist) | | `announcements` | Announcements with audiences, a time window and severity, `active_announcements(tenant)`, `dismiss_announcement(id)` and a broadcast on a Realtime topic. See [Announcements](/docs/blocks/announcements) | | `workflows` | `workflow_runs` for any engine, read through RLS, with a Realtime ping per status change and outbox events when a run ends; cron schedules, counting semaphores, admission control for starts and `purge_workflow_runs`. See [Workflows](/docs/blocks/workflows) | | `workflow-sdk-world` | The Workflow SDK World's tables in a `workflow` schema only the service role reads, a trigger that copies each run into `workflow_runs`, and `dispatch_workflow_deliveries()` for pg\_net delivery on a pg\_cron schedule. See [Workflow SDK](/docs/blocks/workflow-sdk) | | `workflow-builder` | Graph workflows per tenant: definitions, numbered versions checked and published in SQL, webhook, schedule and event triggers, credentials by `credential_ref`, the step library, node-level run status with a Realtime ping, and alerts on failed or slow runs as outbox events. See [Workflow builder](/docs/blocks/workflow-builder) | | `ensure-rls` | An event trigger that enables row level security on every new table outside the Supabase-managed schemas. Install it as `postgres`, which supautils lets create event triggers. See [below](#rls-on-every-new-table) | `invitations` and `organizations` need `tenant` and `access`, so adding one pulls those in as well. They read the active tenant from the source in `sql.modules.access.activeTenant`: by default the tenant the server resolved for the request, then the claim named by `claims.tenant` (`tenant_id` by default). The roles come from `sql.modules.access.roles`. Change modules through `sql.modules` (see [below](#existing-tables-managed-adopt-and-custom)) rather than by editing the files: `sql sync` rewrites them, and doctor reports a hand edit. When the config's [authorization provider](/docs/extending/authorization-providers) has a token hook that owns the `memberships` claim (`tokenHook.ownedClaims`), `sql add tenant` stops, because two hooks would write that claim. Pass `--force` to write it anyway, for example to keep the helpers while you migrate. Modules that need `tenant`, such as `invitations`, are written without `--force`, with `tenant` as a dependency and a note: its memberships table backs `has_organization_role()`, and no hook should call `membership_claims()`. `entitlements` needs `tenant` only with `entitlements.memberships: "tenant"`: with a provider, it reads the provider's `memberIds` functions instead. See [entitlements with an authorization provider](/docs/blocks/entitlements#with-an-authorization-provider). ```sql select better_supabase.track_updated_at('public.customers'); select better_supabase.track_actor('public.customers'); select better_supabase.audit('public.customers', ignore => '{updated_at}'); create policy "admins update" on public.customers for update to authenticated using (organization_id in (select better_supabase.member_organization_ids('{owner,admin}'))) with check (organization_id in (select better_supabase.member_organization_ids('{owner,admin}'))); ``` `member_organization_ids()` returns the user's organizations as a set, so Postgres runs it once per statement and compares each row against the result. Calling `has_organization_role(organization_id)` in a policy runs it once per row instead; keep that one for functions and single checks. `with check` stops an update from moving a row into an organization the user can't manage. Admins can invite members, viewers and other admins. Only an owner, or the service role, can invite an owner, and the invitee has to confirm their email address before `accept_invitation` adds them. Module tables use `gen_random_uuid()` keys. Postgres 18's `uuidv7()` gives time-ordered keys that keep indexes compact, and the modules move to it once hosted Supabase runs Postgres 18 ([supabase/postgres#2051](https://github.com/supabase/postgres/pull/2051)). Errors raised by the modules carry a stable code in the `hint` field, for example `SLUG_RESERVED`, `SLUG_INVALID`, `INVITATION_INVALID`, `INVITATION_EXPIRED`, `INVITATION_ROLE_FORBIDDEN` or `INVITATION_EMAIL_UNCONFIRMED`. They come back as typed `DbError`s through the repository. ## Existing tables: managed, adopt and custom [#existing-tables-managed-adopt-and-custom] Each module has a contract: the functions policies, other modules and the TypeScript side call. `sql.modules..mode` decides what stands behind it. | Mode | The module writes | | ------------------- | ------------------------------------------------------------------------------------------- | | `managed` (default) | its tables, policies and functions | | `adopt` | only functions and views, over tables you already have; never `create table` | | `custom` | nothing: you write the contract functions, and [doctor](/docs/cli/doctor#bs307) checks them | `better-supabase sql list` marks custom modules, and `sql print ` shows the signatures a custom module must provide. Not every module supports every mode; `tenant` and `access` support all three. The other keys rename what the module reads and writes, so an app keeps its own names: ```ts title="better-supabase.config.ts" export default defineConfig({ sql: { modules: { tenant: { mode: "adopt", tables: { memberships: "public.organization_users" }, columns: { memberships: { tenant: "organization_id", lastUsedAt: null }, }, options: { claimFormat: "map" }, }, invitations: { permissions: { invite: "organization.members.invite" }, }, }, }, }); ``` | Key | What it sets | | ------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------- | | `mode` | `managed`, `adopt` or `custom` | | `schema` | the schema of the module's functions and managed tables (`better_supabase`); `jobs` takes none, and `access` keeps its functions in `better_supabase` | | `tables` | logical table to `table` or `schema.table`; `null` for an optional table you don't have | | `columns` | logical table to logical column to column name; `null` for an optional column | | `idType` | the tenant id type: `uuid` (default), `text`, `bigint` or `integer` | | `permissions` | block action to permission key, checked through the [access contract](/docs/blocks/access) | | `options` | module options, listed on each module's page | | `hooks` | where the module looks for the app's [SQL hooks](/docs/extending/events#sql-hooks) | | `events` | `false` stops the module writing its events to the outbox | | `audit` | `false` stops the module writing its actions to the [audit log](/docs/blocks/audit#module-actions) | | `api` | a schema for the Data API that gets entry points for the module's functions (see below) | `organizations` and `profiles` also take `options.extraColumns`: your own columns, keyed by name with their SQL type, which the module adds to its managed table, copies on writes and returns from its reads. A name the module already uses is refused. [Extending blocks](/docs/extending/blocks#your-own-fields) shows how to type them in TypeScript. ### Calling a module over the Data API [#calling-a-module-over-the-data-api] The module schema holds tables and helpers that policies call, so it never belongs in `[api] schemas` (doctor reports it as [BS312](/docs/cli/doctor#bs312)). To call a module from a browser or an isolate without a Postgres connection, give it an API schema: ```ts title="better-supabase.config.ts" sql: { modules: { settings: { api: "api" }, notifications: { api: { schema: "api", functions: ["list_notifications", "notification_counts"] }, }, }, }, ``` `sql add` then writes a `security invoker` function in `api` for each module function that `anon`, `authenticated` or `service_role` may execute, or only those listed in `functions`, with the same name and arguments, granted to the same of those roles. The wrapper runs as the caller, so the module's own permission checks and RLS still apply, and a service function such as `flag_definitions` stays callable by `service_role` only. Each wrapper revokes execute from `public` and from every API role it isn't meant for, so `alter default privileges in schema api grant execute on functions to anon` (a common setup for an exposed schema) doesn't let `anon` call a member or service function. Helpers that a module grants to `authenticated` only so its own policies and triggers can call them get no entry point, and `functions` refuses them: `guard_membership_role` (organizations), `invitation_tenant_ids` and `platform_invitations_readable` (invitations), `comment_subject_readable` (comments), the `attachment_*` and `object_clean` policy helpers (attachments), `incoming_webhook_tenant_ids` and `incoming_webhook_subject_readable` (webhooks-in), `data_export_object_allowed` (data-lifecycle), `mfa_satisfied` (mfa) and `session_active` (sessions). A wrapper an earlier `sql sync` wrote for one of them is dropped. Add the schema to `[api] schemas` in `config.toml` and pass it to the transport: ```ts const client = settings.connect({ transport: rpcTransport(supabase, { schema: "api" }), }); ``` An app server that reaches the database only through PostgREST uses the same wrappers with a service-role client, for example for feature flags and announcements: ```ts const transport = rpcTransport(serviceClient, { schema: "api" }); const flags = createFlagsProvider({ transport }); const announcements = createAnnouncements({ transport }); const outbox = createOutbox(transport, { source: "https://app.example.com" }); ``` An unknown table, column or module, or a mode a module doesn't support, stops `sql add` and `sql sync` with the valid names. Each file's header records the module version and mode (`-- @bs-module tenant@2 adopt`), and the data migration records them in `better_supabase.modules`. An option a module doesn't read stops `sql add` as well, so a misspelled option can't fall back to its default without notice. Only `sql.modules.access` takes the access keys (`model`, `roles`, `functions`, `activeTenant` and the rest). ## Audit log [#audit-log] `audit(table)` records every insert, update and delete of a table. The other parameters name and filter what it writes: ```sql select better_supabase.audit( 'public.customers', ignore => '{updated_at}', redact => '{tax_id}', category => 'billing', event_prefix => 'customer', target_type => 'customer' ); ``` `audit()` keeps these settings on the table's `bs_audit` trigger, as the JSON argument of `audit_row_change`, and writes no rows. So a call in a schema file leaves nothing a schema diff refuses (pg-delta rejects a table that has rows after loading the schema), and the generated migration carries the trigger with its settings; no data migration is needed. You can also write the trigger yourself, which pg-delta orders after the table: ```sql title="supabase/schemas/020_customers.sql" create trigger bs_audit after insert or update or delete on public.customers for each row execute function better_supabase.audit_row_change( '{"ignore": ["updated_at"], "redact": ["tax_id"], "event_prefix": "customer"}' ); ``` The argument takes `ignore`, `redact`, `key_columns` (the primary key by default), `category`, `event_prefix`, `target_type`, `tenant_column` and `label_column`. `audit_settings(table)` returns a table's settings. Tables registered before this release keep their rows in `audited_tables`, and the trigger reads them while it has no argument; calling `audit()` again moves them onto the trigger. `redact` columns are stored as `[redacted]` in the old and new records. The event type is `.created`, `.updated` or `.deleted` (the table name without a prefix), and `tenant_column` overrides the tenant column for this table. Events that are not row changes, like a sign-in or an export, go through `audit_event`. It returns the entry id, and a repeated `idempotency_key` returns the first entry instead of writing a second: ```sql select better_supabase.audit_event( event_type => 'invoice.exported', category => 'billing', tenant => '6d1f...', metadata => '{"format":"csv"}', idempotency_key => 'export-42' ); ``` A job, a webhook handler or an admin tool that records an event as the service role can say who acted and from where: `actor_id`, `actor_kind` (such as `job` or `api-key`), `actor_label`, `request_id` (the request it handled), `scope` (an adopted log's own value, or `tenant` and `platform`), and, with the restricted table, `ip`, `user_agent` and `session_id`. The function takes these only from the service role and direct admin connections; for any other caller it uses `auth.uid()`, the request's JWT and headers, so a user can't record an event in someone else's name. For the service role these three come only from the arguments, never from the request, so a server that calls `audit_event` over the Data API doesn't store its own address and user agent as the user's. An event whose `restricted`, `ip`, `user_agent` and `session_id` are all empty writes no restricted row. Without the restricted table, passing `ip`, `user_agent` or `session_id` fails with `22023`, as `restricted` does: ```sql select better_supabase.audit_event( event_type => 'export.finished', actor_kind => 'job', actor_label => 'Nightly export', ip => '198.51.100.7', user_agent => 'export-worker/1.0', restricted => '{}' ); ``` An app's own `security definer` function that acts for a client, such as a role editor saving permissions, runs as its owner but still carries the client's JWT, so `audit_event` would replace its actor context with the client's. Such a function calls `better_supabase.audit_event_trusted` instead: it takes the same arguments and honours `actor_id`, `actor_kind`, `actor_label`, `scope` and the request details from its caller, with `actor_id` defaulting to `auth.uid()`. Only its owner, the service role and the roles in `trustedRoles` may execute it, so a client can't call it to forge an actor, and the module writes no Data API entry point for it: ```sql create function public.save_role_permissions(role_id uuid, keys text[]) returns void language plpgsql security definer set search_path = '' as $$ begin -- write the permissions, then: perform better_supabase.audit_event_trusted( event_type => 'role.permissions_saved', record_id => role_id::text, actor_kind => case when better_supabase.is_platform('roles.manage') then 'support' else 'user' end, scope => 'platform' ); end; $$; ``` The module options (`sql.modules.audit.options`): | Option | Default | What it does | | --------------------- | ------------------------ | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `tenantColumn` | `plugins.tenant` | the tenant column of audited tables | | `appendOnly` | `true` when managed | triggers reject updates, deletes and truncate; only `purge_audit_log`, running as its owner, deletes | | `readPolicy` | `false` | members read their tenants' entries with the `view` permission, platform staff all of them with `viewAll` (needs [access](/docs/blocks/access)) | | `impersonators` | `show` | `hide` keeps the impersonation columns from `authenticated` | | `restricted` | `false` | old and new records, IP address, user agent and metadata go to a separate `audit_events_restricted` table that only `service_role` reads; without it, `audit_event(restricted => ...)` fails with `22023` | | `eventRoles` | `['service_role']` | the roles that can call `audit_event` | | `trustedRoles` | `[]` | roles besides the owner and `service_role` that can call `audit_event_trusted`, such as the owner role of your definer functions when it isn't the module's owner | | `eventCategory` | `system` | the category of an `audit_event` call without one | | `eventSource` | `app` | the source of an `audit_event` call without one | | `exempt` | `[]` | `schema.table` globs (`public.*_archive`) that [doctor BS315](/docs/cli/doctor#bs315) doesn't expect an audit trigger on | | `tenantLabel` | the organizations module | `schema.table.column` that `tenant_label` copies, matched on `tenantLabelKey` (`id` by default), for apps without the `organizations` module | | `values` | none | adopt mode only: the adopted log's values for `scope`, `actorKind`, `outcome`, `source` and `category`, see below | | `metadataColumns` | none | adopt mode only: the adopted log's own columns that `audit_event` fills from a key of its `metadata`, such as `{ ticket_id: "ticketId" }`; the value is converted to the column's type, a missing key leaves the column to its default, and `list_audit_events` returns the columns under `columns` | | `keepMappedMetadata` | `false` | keeps the keys `metadataColumns` maps in the stored `metadata` too; by default they are removed, since the columns hold them | | `requestIdHeader` | `x-request-id` | the Data API header `request_id` is read from when the `better_supabase.request_id` setting is empty | | `correlationIdHeader` | `x-correlation-id` | the Data API header `correlation_id` is read from when the `better_supabase.correlation_id` setting is empty | `sql add` and `sql sync` read the `audit(...)` calls and `bs_audit` triggers in your schema files and migrations, and write a pgTAP file per audited table to `sql.testsDir` (`supabase/tests/900_better_supabase_audit_public_customers.test.sql`). It checks that the table has the `bs_audit` trigger and is registered with the same `ignore` and `redact` lists, then runs an insert, an update and a delete on a copy of the table and checks the entries: one per change, without the ignored columns, with the redacted values masked. `supabase test db` runs it with your other tests, and `sql sync --check` fails when a registration changed and the file is stale. A later `unaudit(...)` call or `drop table` removes the table's file on the next `sql sync`. Calls with computed arguments, such as `audit(format(...))`, are skipped. Dropping an audited table also drops its registration: the module installs a `sql_drop` event trigger (`bs_audit_forget_dropped`) that deletes the table's row from `better_supabase.audited_tables`, so install the module as `postgres`, which supautils lets create event triggers. `unaudit` takes the table name as text, so a migration can still call `select better_supabase.unaudit('public.old_notes')` after the table is gone; it then clears the registrations of tables that no longer exist. Tables dropped before the event trigger existed keep their rows; the module's data file deletes them, so the next `sql data` migration cleans them up, and [doctor BS322](/docs/cli/doctor#bs322) reports any that remain on a database. An app with its own audit table adopts it: map `sql.modules.audit.tables.log` and `columns.log` to your names, and `null` the columns you don't have (the row snapshots or `supportSession`, for example). The module then writes into your table and never creates it. When the adopted log uses its own words for the same values, map them with `options.values`: the module's value to yours, per column. Writes store your values, and `list_audit_events` (and so `createAuditLog`) reads them back as the module's, so exports and OCSF mapping keep working. Doctor reports the option as migration-only ([BS314](/docs/cli/doctor#bs314)). ```ts title="better-supabase.config.ts" export default defineConfig({ sql: { modules: { audit: { mode: "adopt", tables: { log: "public.activity_log" }, columns: { log: { tenant: "workspace_id", actorKind: "actor_type", tenantLabel: "workspace_name", }, }, options: { values: { scope: { tenant: "workspace", platform: "global" }, actorKind: { user: "member", service: "api" }, outcome: { success: "ok", failure: "error" }, source: { database: "db" }, category: { data: "record", system: "platform" }, }, tenantLabel: "public.workspaces.title", tenantLabelKey: "workspace_id", }, }, }, }, }); ``` `source` covers row changes (`database`) and `audit_event` calls alike, and `category` maps the defaults (`data` for row changes, `eventCategory` for events) and any category a registration or an `audit_event` call names. Values without a mapping are stored as they are. Two module values can't map to the same stored value, because reads couldn't tell them apart. ### Context columns [#context-columns] Every entry also records what an audit page shows, as it was at the time: | Column | What it holds | | ---------------- | --------------------------------------------------------------------------------------------------- | | `actor_kind` | `user`, `service`, `support`, `impersonation`, `oauth-client` or `system`, from the claims | | `actor_label` | the actor's `full_name` metadata or email | | `tenant_label` | the organization's name, with the `organizations` module or `options.tenantLabel` | | `target_label` | the value of the table's `label_column` (`audit(..., label_column => 'title')`), or `audit_event`'s | | `summary` | `audit_event(summary => ...)` | | `request_id` | the request id, see below | | `correlation_id` | `audit_event(correlation_id => ...)`, or the request's correlation id, see below | | `scope` | `tenant`, or `platform` for entries without a tenant | With `restricted`, the restricted table also keeps the `session_id` claim and `changed_values`: `{ column: { old, new } }` for each changed column (every column on insert and delete), redacted like the records and cut at 1000 characters, so one change can be revealed without the whole rows. A managed log has all of these columns; an adopted one writes the ones `columns.log` and `columns.restricted` map. ### Request and correlation ids [#request-and-correlation-ids] Row changes and `audit_event` calls record the request id and the correlation id of the request that made them, so an audit page can show one user action as the event plus the rows it changed. Each id comes from the first of: 1. the `audit_event` argument (`request_id` counts only for the service role and direct admin connections, like the other actor details), 2. the transaction-local setting `better_supabase.request_id` or `better_supabase.correlation_id`, which a server sets over direct Postgres with `set_config(..., true)`, 3. the Data API request header, `x-request-id` or `x-correlation-id` unless the `requestIdHeader` and `correlationIdHeader` options name others. `better_supabase.request_id_or_null(value)` keeps an id of 1 to 128 characters from `A-Z`, `a-z`, `0-9` and `. _ : ; , @ / + = -` and turns anything else into null, so an invalid value falls through to the next source. The ids are metadata a client can set; never use them for access decisions. [`createServer`](/docs/auth/server#request-and-correlation-ids) sends both on every request without configuration. A managed log indexes `correlation_id`. Group an action's entries with the `for_correlation_ids` filter, or in SQL: ```sql select occurred_at, op, event_type, table_name, record_id, changed from better_supabase.audit_events where correlation_id = 'checkout/42' order by occurred_at, id; ``` From TypeScript, `audit.list({ correlationId: ctx.correlationId })` returns the same entries. ### Registering many tables [#registering-many-tables] A project with a hundred tenant tables doesn't write a hundred `audit(...)` lines by hand. `audit_schema_calls` prints them for every table in a schema with the tenant column, minus exempt name patterns: ```sql select better_supabase.audit_schema_calls('public', 'organization_id', '{audit_%,%_archive}'); ``` Paste the output into a schema file, and override single tables with their own `audit(...)` call after it. Static calls keep their place in a pg-delta diff; a loop over the catalog in a schema file runs before the tables exist (doctor [BS318](/docs/cli/doctor#bs318)). For a migration or a one-off script, `audit_schema(schema, tenant_column, exempt)` registers the tables that aren't registered yet and returns how many. Doctor [BS315](/docs/cli/doctor#bs315) flags any table left out. The [audit block](/docs/blocks/audit) reads the log from TypeScript: a list query, NDJSON and OCSF exports, and retention per tenant. In SQL, `list_audit_events` lists the entries the caller can read, and `reveal_audit_entry(entry)` returns an entry's restricted details and records the read as `audit.revealed`. `reveal_audit_entries(entries)` does the same for a list of entry ids, such as one page of an export: it returns the details of the entries the caller may reveal, skips the rest, and records one `audit.revealed` entry per tenant with the ids in `metadata.entries`. ## Retention [#retention] The audit log, the webhook inbox and delivery log, the rate-limit counters and the job archives only grow. Each module has a purge function that deletes rows older than an interval, at most `batch` rows per call (10,000 by default), and returns how many it deleted. Only `service_role` can call them. | Function | Deletes | | -------------------------------------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------ | | `purge_audit_log(older_than => '1 year')` | `audit_events` entries older than `older_than`, or than the tenant's own interval | | `purge_webhooks(older_than => '30 days')` | processed messages; `include_dead => true` also deletes dead ones | | `purge_job_archive(queue, older_than => '7 days', dead_older_than => '30 days')` | completed jobs, and dead jobs after their own retention (`pgmq.a_`, or `job_messages` on the table backend) | | `purge_idempotency_keys()` | expired idempotency keys | | `purge_webhook_deliveries(older_than => '30 days')` | succeeded and canceled outgoing deliveries; `include_dead => true` also deletes dead ones | | `purge_rate_limits()` | counters whose window has ended or whose rule was removed | Schedule them with [pg\_cron](https://supabase.com/docs/guides/cron) at a quiet hour. Enable the extension once (`create extension pg_cron with schema pg_catalog`), then: ```sql select cron.schedule('purge-audit-log', '15 3 * * *', $$select better_supabase.purge_audit_log()$$); select cron.schedule('purge-webhooks', '30 3 * * *', $$select better_supabase.purge_webhooks()$$); select cron.schedule('purge-emails-archive', '45 3 * * *', $$select better_supabase.purge_job_archive('emails')$$); select cron.schedule('purge-idempotency-keys', '0 4 * * *', $$select better_supabase.purge_idempotency_keys()$$); select cron.schedule('purge-webhook-deliveries', '45 3 * * *', $$select better_supabase.purge_webhook_deliveries()$$); select cron.schedule('purge-rate-limits', '*/15 * * * *', $$select better_supabase.purge_rate_limits()$$); ``` Tenants can keep entries for different periods, a plan's retention for example. Write an `audit_retention(tenant)` function that returns an interval (or null for the default) in the schema `sql.modules.audit.hooks` points at, and `purge_audit_log` uses it for every entry. Like every SQL hook, it is optional: the module calls hooks through dynamic SQL, so `supabase db lint` passes without them. To decide in TypeScript instead, call `purgeAuditLog` from [`better-supabase/blocks/audit`](/docs/blocks/audit) in a scheduled job: ```ts import { purgeAuditLog } from "better-supabase/blocks/audit"; await purgeAuditLog(postgres.admin, { olderThan: "1 year", retention: async (tenant) => tenant ? await plans.auditDays(tenant) : undefined, }); ``` When a table holds more old rows than one batch, the next run picks up the rest; run the job more often, or raise `batch`, until it keeps up. Keep the webhook retention above your senders' retry window: a retried message whose id was purged is stored and processed again. ## Support sessions [#support-sessions] `support-sessions` records who viewed the app as whom, for [support mode](/docs/auth/impersonation). It needs `access` and `audit`. `start_support_session(target, reason, ttl, read_only, metadata, tenant)` checks `is_platform('support.start')` for the caller, ends the caller's previous session, writes `support.started` to the audit log and returns the session as jsonb. An admin has one active session (a unique index). It refuses a target with platform permissions and a writable session unless the options below allow them. `end_support_session(session_id)` ends it for its admin (`ended_by` is `admin`), a caller with `support.revoke` (`revoked`) or the service role, which alone passes `ended_by`. `active_support_session` called as the admin checks `support.start` again. Errors carry `SUPPORT_FORBIDDEN`, `SUPPORT_SELF`, `SUPPORT_TARGET_MISSING`, `SUPPORT_TARGET_PLATFORM`, `SUPPORT_WRITES_DISABLED`, `SUPPORT_TTL` or `SUPPORT_REASON_REQUIRED` in the `hint`. | Option | Default | What it does | | ---------------------- | --------- | ------------------------------------------------------------------------ | | `maxTtl` | `4 hours` | the longest session `start_support_session` accepts | | `requireReason` | `true` | refuses a start without a reason | | `allowWrites` | `false` | accepts `read_only => false` | | `allowPlatformTargets` | `false` | lets a session target platform staff | | `auditCategory` | `support` | the audit log category of `support.started` and `support.ended` | | `claimsHook` | none | `schema.function` of your access token hook, for `support_target_claims` | The permission keys are `support.start`, `support.read` and `support.revoke`; rename them with `blocks["support-sessions"].permissions`. The hooks `before_support_start(admin, target, reason, metadata)`, `after_support_start(session_id)` and `after_support_end(session_id)` run in the schema `hooks.schema` names. An existing sessions table can be adopted: the `tenant`, `readOnly`, `endedBy` and `metadata` columns are optional. ```ts title="better-supabase.config.ts" sql: { modules: { "support-sessions": { mode: "adopt", tables: { sessions: "public.impersonation_sessions" }, columns: { sessions: { admin: "impersonator_id", metadata: null } }, permissions: { start: "system.users.impersonate" }, options: { claimsHook: "public.custom_access_token_hook" }, }, }, }, ``` ## Rate limiting writes [#rate-limiting-writes] Auth rate-limits sign-ins, but the Data API has no limit of its own: a signed-in user can call `insert` or an RPC as often as they like. The `rate-limit` module counts writes in PostgREST's [pre-request hook](https://docs.postgrest.org/en/v12/references/transactions.html#pre-request), before the query runs: ```bash better-supabase sql add rate-limit ``` ```sql -- 5 invites per user per minute, 300 writes per user per minute overall. select better_supabase.set_rate_limit('/rpc/send_invite', 5, interval '1 minute'); select better_supabase.set_rate_limit('*', 300, interval '1 minute'); -- Per tenant instead of per user: count by the tenant_id claim. select better_supabase.set_rate_limit('/customers', 1000, interval '1 hour', key_claim => 'tenant_id'); -- Remove a rule. select better_supabase.set_rate_limit('/customers', null); ``` * A scope is `*`, a table path (`/customers`) or an RPC path (`/rpc/send_invite`). Every matching rule counts, so `*` and a path rule both apply. * Only `POST`, `PATCH`, `PUT` and `DELETE` count. GET and HEAD run read-only and may be served by a [read replica](/docs/guides/read-replicas). A `POST` to `/rpc` for a `stable` or `immutable` function also runs in a read-only transaction, so `check_request()` skips it instead of failing the call. * Callers without the claim (anonymous) are counted by the right-most `x-forwarded-for` hop, the one the API gateway appends; clients can forge the hops before it. The service role is never limited. * Counters live in an unlogged table: fast, and reset after a crash. * A write over the limit fails with HTTP 429 and a `Retry-After` header. The repository returns it as a `rate_limited` [`DbError`](/docs/concepts/results) with `retryAfter` in seconds, and `problemResponse()` passes the header on. The module's data file sets `pgrst.db_pre_request` on the `authenticator` role, and only if no other pre-request function is set. Neither diff engine captures role settings, so the setting ships in the migration `sql data` writes. Doctor warns (BS313) when a live database has no pre-request hook, or one that doesn't call `check_request()`. If you already have a pre-request function, keep it and call the check from it. Put it in a schema the Data API doesn't expose, so clients can't call it through `/rpc`. Set `options.preRequest: false` to leave the setting to your own function: the data file then removes a setting that still points at `check_request()` instead of adding one. ```ts title="better-supabase.config.ts" export default defineConfig({ sql: { modules: { "rate-limit": { options: { preRequest: false } } } }, }); ``` ```sql create schema if not exists private; create or replace function private.pre_request() returns void language plpgsql as $$ begin perform better_supabase.check_request(); -- your checks end $$; ``` The hook runs only for the Data API. Queries through [`better-supabase/postgres`](/docs/auth/postgres) and `ctx.sql` aren't limited. ### Limits in route handlers [#limits-in-route-handlers] Public endpoints often limit by something other than a claim: a token in the URL, an organization, an AI chat thread. `hit_rate_limit(scope, key)` counts one hit of `key` with the same rules and counters, and `createRateLimit` from `better-supabase/blocks/jobs` calls it from a route handler. `rateLimited()` answers a refused hit with a `problem+json` 429 and `Retry-After`: ```ts import { createRateLimit, rateLimited } from "better-supabase/blocks/jobs"; const limits = createRateLimit(postgres.admin); export async function POST( request: Request, { params }: { params: { token: string } }, ) { const hit = await limits.check("public-webhook", params.token).orThrow(); if (!hit.allowed) return rateLimited(hit, { instance: new URL(request.url).pathname }); // handle the request } ``` Apps that reach the database over the Data API instead of a direct connection pass a service-role `BlockTransport`, like the other blocks: `createRateLimit(rpcTransport(adminClient))`. It calls `check_rate_limit(scope, key, max_requests, period)`, which returns the same decision as JSON; with `sql.modules.rate-limit.api` set, pass the API schema as `rpcTransport(adminClient, { schema: "api" })`. The limit is the scope's rule from `set_rate_limit`, or one given per call (`check("chat", threadId, { max: 20, period: "1 minute" })`). A scope with neither fails with `RATE_LIMIT_UNKNOWN`. Counters are shared by every instance, unlike an in-memory map on serverless functions. `purge_rate_limits()` removes counters without a rule after a day, so keep a per-call `period` under a day or define the rule. ## Enforcing jsonb shapes [#enforcing-jsonb-shapes] Give a `json` entry a `schema` and `sql add jsonb-schemas` adds a [pg\_jsonschema](https://supabase.com/docs/guides/database/extensions/pg_jsonschema) check constraint, so the database rejects rows the generated types would reject. `schema` takes a JSON Schema object, or any Standard JSON Schema value (zod 4, valibot, arktype): ```ts title="better-supabase.config.ts" import { toStandardJsonSchema } from "@valibot/to-json-schema"; import * as v from "valibot"; export const CustomerMetadata = toStandardJsonSchema( v.object({ source: v.string(), tier: v.optional(v.picklist(["free", "pro"])), }), ); export default defineConfig({ json: { "customers.metadata": { import: "./lib/metadata.ts#CustomerMetadata", schema: CustomerMetadata, }, }, sql: { modules: ["jsonb-schemas"] }, }); ``` Each constraint is added `not valid` and then validated in its own statement, so existing rows that don't match make the migration fail. Fix those rows first. On a large table, move the `validate constraint` statement to a later migration: adding a `not valid` check blocks writes only briefly, and validating takes a lock that lets writes continue. Re-run `sql add` after you change a schema. Next to each constraint, a `before insert or update` trigger of the same name calls pg\_jsonschema's `jsonschema_validation_errors`. A row that fails raises SQLSTATE `23514` with the hint `JSON_SCHEMA_INVALID` and the errors as a JSON array in `DETAIL`, so the repository returns a `validation` error with one issue per schema error, at the column's database name: ```ts // metadata came from a request body as { source: "web", tier: "gold" } const result = await db.customers.update(id, { metadata }); if (!result.ok && result.error.kind === "validation") { result.error.issues; // [{ message: '"gold" is not one of ["free","pro"]', path: ["metadata"] }] } ``` The check constraint stays for rows written while triggers are off, such as a restore with `session_replication_role = replica`; its failures also map to `validation`, with one issue for the whole value. ## RLS on every new table [#rls-on-every-new-table] `sql add ensure-rls` installs an event trigger that enables row level security on every table created with `create table`, `create table as` or `select into`, so a table never reaches the Data API without RLS. Tables in the Supabase-managed schemas (`auth`, `storage`, `realtime`, `extensions` and the others) and in `pg_*` schemas are skipped. ```sql create table public.notes (id bigint primary key, body text); -- relrowsecurity is now true; add policies before granting access. ``` A table with RLS and no policies denies every API role, so add its policies in the same migration. Apply the migration as `postgres`: event triggers need a superuser, and on Supabase the supautils extension lets `postgres` create them. Tables that already exist keep their setting. ## Keeping files current [#keeping-files-current] Every file starts with a header that marks it as managed. Its number comes from the module's position in the registry, so its path stays the same when you add other modules. List the modules you use in the config: ```ts title="better-supabase.config.ts" export default defineConfig({ sql: { modules: ["audit", "jobs", "invitations"], testsDir: "supabase/tests", }, }); ``` `better-supabase sql sync` rewrites those files after an upgrade, and `sql sync --check` fails in CI when a file is out of date. Use `sql print ` to copy a module into a hand-written migration instead. `sql add`, `sql sync` and `sql data` take `--dry-run` to show what they would write, and `--tests-dir ` overrides `sql.testsDir` for the pgTAP module. | Option | Default | | -------------- | --------------------- | | `sql.dir` | `supabase/schemas` | | `sql.prefix` | `900_better_supabase` | | `sql.testsDir` | `supabase/tests` | | `sql.modules` | `[]` | ## Upgrading modules [#upgrading-modules] When a release changes a module in a way the schema diff can't follow (a renamed column, a backfill that must run before a new constraint), the module's version goes up and the release ships a forward step. Run the upgrade after you update the package: ```bash better-supabase sql upgrade ``` It reads the version from each file's `@bs-module` line (a file without one, written before the line existed, counts as version 1). It then writes the forward steps into `_better_supabase_block_upgrade.sql` in the `migrations` folder next to your `config.toml` and rewrites the module files. Create the schema migration afterwards, so the steps run first. `sql upgrade --check` exits with 1 when a module is behind or a file is stale, and `--dry-run` lists the steps without writing. The pgTAP files a module writes for your tables (the audit tests, say) carry no module version; `sql sync --check` keeps them current, and `sql upgrade` leaves them out. A renamed function, table or claim keeps working for at least one minor version. The module file keeps a wrapper under the old name (a view for a table), marked `-- Deprecated since`, and doctor reports code that still uses it (BS309). A renamed column has no wrapper: `sql upgrade` renames it, and doctor reports the old name in your SQL before you upgrade. The [stability page](/docs/extending/stability#sql-module-objects-and-claims) lists what each module guarantees. ## Existing triggers [#existing-triggers] `track_updated_at()` and `audit()` warn when the table already has a trigger that does the same work (a `moddatetime` or `touch_updated_at` trigger, or another audit trigger), because both would run on every write. Pass `replace_trigger => true` to drop the existing trigger in the same call: ```sql select better_supabase.track_updated_at('public.customers', replace_trigger => true); select better_supabase.audit('public.customers', replace_trigger => true); ``` Doctor reports a table that still has both (BS310). ## Rendering modules in code [#rendering-modules-in-code] `better-supabase/sql` exports what the `sql` command uses, for build scripts and tests that write the files themselves. `moduleLayout(config)` returns the paths, `renderModules` returns the files with their managed headers (dependencies included), and `resolveModules` lists the modules a set of names pulls in. ```ts title="scripts/write-block.ts" import { resolveConfig } from "better-supabase/config"; import { moduleLayout, renderModules, resolveModules, } from "better-supabase/sql"; import config from "../better-supabase.config.ts"; const layout = moduleLayout(resolveConfig(config, process.cwd())); const modules = resolveModules(["audit", "jobs"], layout).map((m) => m.name); const files = renderModules(["audit", "jobs"], layout); ``` `compileReadSets` turns the read sets in `generated.ts` into the SQL views and functions that `better-supabase gen` writes. `moduleBody(name, layout)` returns one module's SQL without its header, `customContracts` the functions custom-mode modules must provide, and `moduleFileVersion(contents)` the version and mode in a file's header. `moduleFilePaths(names, layout)` maps each module a set of names pulls in to its schema and test paths without rendering it. `modulePermissionKeys(blocks, names)` lists the permission keys those modules check, with the action, the key after `sql.modules..permissions` and whether a tenant or a platform check asks for it. Under `sql.modules.access.model: 'provider'`, `sql add` and doctor (BS411) check each key against the provider's `permissions`; a script can do the same: ```ts title="scripts/check-module-keys.ts" import { resolveConfig } from "better-supabase/config"; import { modulePermissionKeys } from "better-supabase/sql"; import config from "../better-supabase.config.ts"; const { sql, authorization } = resolveConfig(config, process.cwd()); const scopeOnly = new Set( (authorization?.permissions ?? []) .filter((entry) => entry.sqlComplete === true) .map((entry) => entry.key), ); for (const { module, action, key } of modulePermissionKeys( sql.modules, sql.moduleNames, )) { if (!scopeOnly.has(key)) console.error(`${module}.${action}: ${key}`); } ```