Tenant Isolation & RLS

How RLS Works in SMTA

SMTA enables RLS on all core tables and writes policies that call core.get_current_user_id() to identify the authenticated user. If the user is not part of an organization or unit, the policy returns false and the user sees no rows.

The Membership Check

The canonical RLS pattern in SMTA uses a helper function core.is_org_member(organization_id):

create policy "org members only" on core.some_table
  for select using (
    is_deleted = false AND core.is_org_member(organization_id)
  );

This pattern appears across all org-scoped tables. A user sees a row only if they are an active (non-deleted) member of that row’s organization.

core.get_current_user_id()

This function is the keystone of SMTA’s RLS system. Every policy ultimately relies on it. It returns the UUID of the currently authenticated user. The implementation is adapter-specific:

• Supabase adapter: calls auth.uid(), which reads the user ID from the validated Supabase JWT
• Payload adapter: reads the app.current_user_id PostgreSQL session variable, which your middleware sets at the start of each request
• better-auth adapter: same session-variable approach as Payload — withSMTA() sets app.current_user_id via SET LOCAL at the start of each request

Because RLS policies reference the function by name rather than by body, switching adapters requires only replacing the function implementation — the policies themselves don’t change.

Platform Lockdown

The platform schema is where SMTA stores data related to the SaaS business itself. This is a highly sensitive area in that it allows administrators to manage all tenants.

This schema is locked down by an additional RLS layer: platform tables are accessible only to app_admin (the BYPASSRLS migration/admin role). End users and application code connecting as app_user cannot read or write platform tables at all. (On Supabase, app_admin/app_user map onto service_role/authenticated.)

Soft Deletion and RLS

SMTA uses soft deletion throughout — records have an is_deleted boolean column. RLS policies filter out soft-deleted rows automatically:

is_deleted = false AND core.is_org_member(organization_id)

This means:

• Deleted organizations and memberships are invisible to queries
• Data is recoverable by a service-role admin query
• Audit logs are never soft-deleted — they are append-only

Your App Schema

SMTA’s RLS extends naturally into your app schema. Each table needs an org_id or unit_id column to associate rows with a tenant, but membership is validated automatically — application queries need no explicit membership checks.

Use the same helper functions SMTA itself uses internally:

Helper	Checks	Use when
core.is_org_member(org_id)	User has an active membership in the org	Data belongs to the whole org
core.is_unit_member(unit_id)	User is explicitly assigned to this unit	Data is private to that unit’s staff
core.is_org_member_for_unit(unit_id)	User is a member of the org that owns this unit	Org-wide visibility via a unit FK

-- Org-scoped: any org member can access
alter table app.your_table enable row level security;

create policy "org members" on app.your_table
  for all using (core.is_org_member(org_id));


-- Unit-scoped (narrow): only staff explicitly assigned to this unit
alter table app.unit_table enable row level security;

create policy "unit members" on app.unit_table
  for all using (core.is_unit_member(unit_id));

-- Unit-scoped (broad): any org member, resolved via the unit's parent org
-- create policy "org members via unit" on app.unit_table
--   for all using (core.is_org_member_for_unit(unit_id));

For a detailed walkthrough of how to decide which column to use, how to handle FK child tables, and how to prevent tenant references from drifting, see Structuring Your App Schema.