SQL modules

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.

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/<stamp>_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 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 engineCopy Anchor Link

Without that table, the Supabase CLI diffs with migra, and doctor 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 lists the steps to move to pg-delta.

ModulesCopy Anchor Link

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) rather than by editing the files: sql sync rewrites them, and doctor reports a hand edit.

When the config's authorization provider 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.

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

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 DbErrors through the repository.

Existing tables: managed, adopt and customCopy Anchor Link

Each module has a contract: the functions policies, other modules and the TypeScript side call. sql.modules.<module>.mode decides what stands behind it.

better-supabase sql list marks custom modules, and sql print <module> 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:

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

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 shows how to type them in TypeScript.

Calling a module over the Data APICopy Anchor Link

The module schema holds tables and helpers that policies call, so it never belongs in [api] schemas (doctor reports it as BS312). To call a module from a browser or an isolate without a Postgres connection, give it an API schema:

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:

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:

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 logCopy Anchor Link

audit(table) records every insert, update and delete of a table. The other parameters name and filter what it writes:

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:

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 <event_prefix>.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:

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:

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:

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

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

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 columnsCopy Anchor Link

Every entry also records what an audit page shows, as it was at the time:

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 idsCopy Anchor Link

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

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 tablesCopy Anchor Link

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:

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). 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 flags any table left out.

The audit block 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.

RetentionCopy Anchor Link

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.

Schedule them with pg_cron at a quiet hour. Enable the extension once (create extension pg_cron with schema pg_catalog), then:

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 in a scheduled job:

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 sessionsCopy Anchor Link

support-sessions records who viewed the app as whom, for support mode. 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.

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.

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 writesCopy Anchor Link

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, before the query runs:

better-supabase sql add rate-limit

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

export default defineConfig({
  sql: { modules: { "rate-limit": { options: { preRequest: false } } } },
});

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 and ctx.sql aren't limited.

Limits in route handlersCopy Anchor Link

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:

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 shapesCopy Anchor Link

Give a json entry a schema and sql add jsonb-schemas adds a 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):

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:

// 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 tableCopy Anchor Link

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.

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 currentCopy Anchor Link

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:

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 <module> 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 <dir> overrides sql.testsDir for the pgTAP module.

Upgrading modulesCopy Anchor Link

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:

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 <timestamp>_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 lists what each module guarantees.

Existing triggersCopy Anchor Link

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:

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 codeCopy Anchor Link

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.

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.<module>.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:

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