Organizations and invitations

The organizations and invitations SQL modules put the rules for teams in Postgres: who may rename an organization, who may invite whom and with which role, that an organization always keeps an owner, and that an invitation is accepted once by the address it was sent to. better-supabase/blocks/organizations calls those functions from TypeScript, returns a Result for each call and emits block events.

pnpm better-supabase sql add organizations invitations

Both modules need tenant (the memberships table) and access (the access contract), so sql add pulls those in too. Permissions are checked with member_can(), so the same functions work with the default roles, your own role and permission tables, an authorization provider or your own functions.

FunctionsCopy Anchor Link

The name in parentheses is the block action whose permission the function checks. create_invitation(organization, email, role) from 0.4 still works and returns the token only.

Every check runs in the function, and the database keeps two rules even for writes that bypass the functions. A deferred trigger refuses a commit that leaves an organization without an owner (ownerInvariant), and a trigger on the memberships table stops members from changing their own role or assigning a role above their own. That guard checks only client writes (made as anon or authenticated): the service role, direct admin connections and security definer functions pass it, so your own functions that write memberships (an invitation accept that seats the invitee, a one-statement ownership transfer) work, and check their own ceilings as the module's functions do.

When another trigger already guards an adopted memberships table, such as an authorization provider's assignment rules, set assignmentGuard: "external": the module drops its own trigger, so a role change is checked once and fails with one error vocabulary. The module's functions still check before they write: update_member_role refuses a change to the caller's own role (ORGANIZATION_SELF_ROLE) and needs can_assign for both the member's current role and the new one (ORGANIZATION_ROLE_CEILING), and remove_member needs it for the current role. Those checks hold even when the external guard skips writes from security definer functions. A managed memberships table always keeps the module's guard.

transfer_ownership promotes the new owner and demotes the calling owner in one update, so a statement-level rule on the number of owners sees the transfer as a whole, and it refuses a new owner whose account is disabled (ORGANIZATION_FORBIDDEN) or whose membership is suspended (ORGANIZATION_MEMBER_SUSPENDED).

Suspending membersCopy Anchor Link

With a disabledAt column on the memberships table (managed tables have it; an adopted one maps sql.modules.tenant.columns.memberships.disabledAt, see suspended memberships), suspend_member(organization, member) switches a member off in one organization without removing them. They keep their role and their row, get no permissions there, and resume_member gives everything back.

Both functions check the suspendMember action, which defaults to the removeMember key (members.remove, or what sql.modules.organizations.permissions.removeMember sets), and the role ceiling: the caller must be able to assign the member's role (ORGANIZATION_ROLE_CEILING). They refuse the caller's own membership (ORGANIZATION_SELF), and suspend_member refuses the last active owner (ORGANIZATION_OWNER_REQUIRED). A suspended owner doesn't count as one, so the deferred owner check also refuses a commit that leaves only suspended owners. Each returns false when nothing changed, calls the after_member_change hook with suspended or resumed, writes an organization.member_suspended or organization.member_resumed audit entry when the audit module is installed, and emits the same event.

The role guard on the memberships table treats a client write that changes disabled_at like a role change: nobody suspends or resumes themselves, or a member above their own role.

From TypeScript, suspendMember(organizationId, userId) and resumeMember(organizationId, userId) return whether the state changed, and members() and mine() carry disabledAt (a Temporal.Instant) for a suspended membership. To lock a user out of every organization and end their sessions, suspend the account instead (see suspending an account).

The owner check locks the organization row, so two owners who leave at the same time can't both succeed. When the block owns the organizations table, the slug is required, and memberships, invitations and permission overrides reference the organization with on delete cascade: deleting it deletes them. Under model: 'catalog', a role that a member still holds can't be deleted (on delete restrict). The keys are added to tables that already exist too; when older rows have no organization, the block adds the key without validating them and logs a warning.

From TypeScriptCopy Anchor Link

createOrganizations takes a transport that runs the functions as the user. mine(), members(organizationId) and invitations(organizationId) list the caller's organizations, an organization's members and its open invitations. sqlTransport uses a Postgres connection with the user's claims, and works whatever schema the modules are in:

"use server";
import {
  createOrganizations,
  sqlTransport,
} from "better-supabase/blocks/organizations";
import { betterSupabase } from "@/lib/supabase/client";
import { postgres } from "@/lib/supabase/postgres";
import { bs } from "@/lib/supabase/server";
import { sendInvitationEmail } from "@/lib/email";

export async function inviteMember(organizationId: string, email: string) {
  const ctx = await bs.context();
  if (ctx.auth.kind !== "user") throw new Error("Sign in first");
  const organizations = createOrganizations({
    transport: sqlTransport(postgres.asUser(ctx.auth.claims)),
    events: betterSupabase.events,
    actorId: ctx.auth.user.id,
    canInvite: async () => (await seatsLeft(organizationId)) > 0,
    onInvite: ({ invitation, token }) =>
      sendInvitationEmail(invitation.email, `/invite/${token}`),
  });
  return organizations.invite({ organizationId, email, role: "member" });
}

rpcTransport(ctx.supabase) calls them over the Data API instead. Never add better_supabase or a sql.modules.<module>.schema to [api] schemas: those schemas hold internal helpers that policies call, and doctor reports exposing them as BS312. Set api: "api" on the organizations and invitations modules instead: sql add writes a security invoker entry point in api for each function the modules grant to authenticated, with the same name and arguments. Add api to [api] schemas and pass rpcTransport(ctx.supabase, { schema: "api" }), or schema: "api" to createOrganizations. With entry points in different schemas, pass schema: { organizations: "api", invitations: "app_api" }.

sql: {
  modules: {
    organizations: { api: "api" },
    invitations: { api: "api" },
  },
},

Each method returns an AsyncResult. Errors the block raises are DbErrors whose hint is a stable code, so a form can show the right message:

const result = await organizations.create({ name, slug });
if (!result.ok && result.error.hint === "ORGANIZATION_SLUG_TAKEN") {
  return { field: "slug", message: "That address is taken" };
}

The token is only in the onInvite argument and the invite result. It is never part of an event, and by default only its SHA-256 hash is stored.

The invitation in onInvite carries what an email needs without another read. organization holds the id and previewColumns, extra holds the keys an invitation_preview_extra hook adds (a role label, say, see existing tables), and, with the profiles module installed, inviter holds the inviter's id and public profile fields (username, fullName, firstName, lastName and avatar, as the profiles module maps them). An email template can say who invited the person to which role:

onInvite: ({ invitation, token }) =>
  sendInvitationEmail(invitation.email, {
    link: `/invite/${token}`,
    inviter: String(invitation.inviter?.["fullName"] ?? "A teammate"),
    role: String(invitation.extra["roleLabel"] ?? invitation.role),
  }),

invite_member, resend_invitation, update_invitation and my_invitations return the same keys in SQL. Without the profiles module, inviter is null; return the inviter's name from the hook instead (it receives the invitation id, and invited_by is on the row).

Your own fields and hooksCopy Anchor Link

Columns from options.attributes and options.extraColumns come back from mine() next to id, name, slug and role. Pass a Standard Schema for them as fields to type them, parse them on read and validate create and update, and hooks to refuse, rewrite or observe any method:

const organizations = createOrganizations({
  transport,
  fields: z.object({ plan: z.enum(["free", "pro"]) }),
  hooks: {
    create: {
      before: ([attrs, options]) => [{ plan: "free", ...attrs }, options],
    },
  },
});

const [first] = await organizations.mine().orThrow();
first?.plan; // "free" | "pro"

SQL hooks run inside the functions for every caller: before_organization_create(attrs jsonb, owner uuid), after_organization_create(organization, owner uuid), before_organization_update(organization, attrs jsonb) and after_organization_update(organization, attrs jsonb). Extending blocks covers all of it.

Switching organizationsCopy Anchor Link

switch_organization(organization) checks the caller is a member and the organization is active, then follows sql.modules.access.activeTenant:

When refresh is true, refresh the session so the next access token carries the new claim. If your access token hook builds the claim from somewhere else, use the profileColumn or resolver source instead.

OptionsCopy Anchor Link

sql.modules.organizations.options:

permissions.create makes creating an organization a platform permission, checked with is_platform(). Without it every signed-in user can create one. permissions.updatePlatform and permissions.deletePlatform name platform keys that let platform staff update or delete any organization without the service role, next to the members who hold update or delete in it. permissions.updateRolePlatform and permissions.removeMemberPlatform do the same for update_member_role and remove_member: platform staff with that key pass the permission check, and the role ceiling still applies, so the access model's can_assign must allow them the roles they change.

sql.modules.invitations.options (tokenStorage: "plain" is migration-only: the config accepts it in mode: "adopt" only, and doctor warns about it, BS314):

Accepting checks the inviter again: an inviter who lost the invite permission, or a permission the role grants, can no longer bring someone in with it.

An invitation with a null organization is a platform invitation. It needs sql.modules.access.model: "catalog" with a platformAssignments table, and lives in its own platform_invitations table. The inviter needs the invitePlatform permission (platform.invite) and every permission the platform role grants, both when inviting and when the invitation is accepted. Managed catalog roles have a scope column (tenant or platform), and neither kind of role is assignable as the other. An adopted catalog maps sql.modules.access.columns.roles.scope and names its values in sql.modules.access.options.tenantRoleScope and platformRoleScope; an adopted invitations table that also holds platform invitations (rows without an organization) maps tables.platformInvitations to itself.

Under the provider model, name the table that holds platform role assignments in sql.modules.invitations.options.platformRoles:

invitations: {
  options: {
    platformRoles: {
      table: "public.user_roles",
      user: "user_id",
      role: "role_id",
      // The role column holds a roles table id; the role name is its key.
      // where names the platform roles when the table holds tenant roles too.
      through: {
        table: "public.roles",
        id: "id",
        column: "key",
        where: "{row}.scope = 'system'",
      },
      // Optional: who may assign which platform role. {role} is the name.
      canAssign: "authz.can_assign_platform_role({user}, {role})",
    },
  },
},

A platform invitation's role resolves only among the roles through.where accepts ({row} is the roles row), so a tenant role's id or key is refused with INVITATION_ROLE_UNKNOWN, when inviting and again at accept. When through.table is also the tenant module's roleThrough table, where is required there too (roleThrough.where, such as "{row}.scope = 'organization'"), so organization invitations, accept and update_member_role resolve only tenant roles; see membership roles stored as ids. With through.where, a bs_role_scope trigger on platformRoles.table also refuses a direct write of any other role, from the service role too, with PLATFORM_ROLE_SCOPE.

The inviter needs invitePlatform through the provider's isPlatform. Without canAssign, that permission alone lets them invite to any platform role. Accepting inserts the assignment into the table. Platform invitations can share the tenant invitations table (map tables.platformInvitations to the same table): their rows have no tenant, the role is stored in the role column's own type (a uuid through the roles table, say), and prefill is written when the table has that column. When the provider has isPlatformFor, accept checks the inviter again through it, never the invitee: a canAssign with {user} runs with the inviter, a canAssign that reads the caller (no {user}, such as authz.can_assign_platform({role})) is checked only when they invite, unless canAssignFor gives the inviter's form, such as "authz.can_assign_platform_for({user}, {role})". Without isPlatformFor, the inviter is checked when they invite, not again at accept.

Accepting from the appCopy Anchor Link

An in-app inbox can't hold the invitation token, since only its hash is stored. accept_invitation_by_id(invitation_id) and decline_invitation_by_id(invitation_id) do the same as the token functions for the signed-in user whose confirmed email the invitation names; anyone else gets INVITATION_EMAIL_MISMATCH on accept and false on decline. my_invitations() lists the open invitations for the caller's confirmed email, tenant and platform ones, without their tokens, so the inbox knows what to show. Each one carries created_at, its organization (the id plus previewColumns, null for a platform invitation), the inviter and the keys an invitation_preview_extra hook adds, so the inbox needs no read policy on the invitations table. In TypeScript, organizations.myInvitations() returns them as createdAt, organization, inviter and extra, and acceptInvitationById(id) and declineInvitationById(id) answer them.

Editing an invitationCopy Anchor Link

update_invitation(invitation_id, invitee_email, invitee_role, prefill) changes an open invitation in place, so apps don't need a client update policy on the invitations table. A null argument keeps the current value. It makes the checks invite_member makes: the caller needs invite in the organization (invitePlatform for a platform invitation), may assign both the current and the new role (INVITATION_ROLE_FORBIDDEN), and a new address must not belong to a member (INVITATION_ALREADY_MEMBER). Another open invitation for the new address is replaced. The token and expiry stay, so the link already sent keeps working: call resend_invitation to mail a new link to a changed address. An expired invitation is refused with INVITATION_EXPIRED, as accept refuses it; call resend_invitation first to renew its expiry, then edit it. It returns the invitation without its token and emits invitation.updated. In TypeScript:

await organizations.updateInvitation(invitationId, {
  role: "admin",
  prefill: { name: "Ada Lovelace" },
});

Existing tablesCopy Anchor Link

Both modules support adopt, so they run over the tables you have. Map the names and set the columns you don't have to null:

export default defineConfig({
  sql: {
    modules: {
      access: { model: "catalog" },
      tenant: {
        mode: "adopt",
        tables: { memberships: "public.organization_users" },
        columns: {
          memberships: { tenant: "organization_id", role: "role_id" },
        },
      },
      organizations: {
        mode: "adopt",
        tables: { organizations: "public.organizations" },
        columns: { organizations: { createdBy: null, deletedAt: null } },
        options: { attributes: ["logo_url"] },
      },
      invitations: {
        mode: "adopt",
        tables: { invitations: "public.organization_invitations" },
        columns: {
          invitations: {
            tenant: "organization_id",
            role: "role_id",
            tokenHash: "token",
          },
        },
        options: { tokenStorage: "plain" },
      },
    },
  },
});

Under the catalog access model, roles are ids in your roles table, and the functions accept a role id or key. The before_organization_create, after_organization_create, after_member_change, before_invitation_create and after_invitation_accept SQL hooks run your own checks and side effects in the same transaction.

invitation_preview_extra(invitation uuid) returns jsonb adds keys to what invitation_preview returns, such as the role's display name or branding from another table, and to every invitation invite_member, resend_invitation, update_invitation and my_invitations return. Its keys are merged over the preview's, and it runs as the module function's owner, since the preview is callable without a session:

create function public.invitation_preview_extra(invitation uuid)
returns jsonb
language sql
stable
set search_path = ''
as $$
  select jsonb_build_object('roleLabel', r.label)
  from public.invitations i join public.roles r on r.id = i.role_id
  where i.id = invitation
$$;

In TypeScript, previewInvitation(token) returns the keys the hook added in extra (preview.extra.roleLabel), next to the fixed fields.

ErrorsCopy Anchor Link

INVITATION_EXPIRED and INVITATION_INVALID share the SQLSTATE P0002, so code that checks only the SQLSTATE sees no change; check the hint to tell an expired invitation, which resend_invitation renews, from one that is gone. In TypeScript, InvitationErrorHint lists the invitation codes, and a failed call carries one as error.hint:

const accepted = await organizations.acceptInvitation(token);
if (!accepted.ok && accepted.error.hint === "INVITATION_EXPIRED") {
  // Ask the organization to resend the invitation.
}