Md Tarek — Portfolio© 2026

Full-stack Developer

System Design — built to hold up.

dhaka → remote, worldwide

Loading experience

Md TarekTarek
Dev

Supabase Architecture Row Level Security and Realtime Guide

By: Md Tarek · 20 Oct 2026

Supabase Architecture Row Level Security and Realtime production topology
Supabase Architecture Row Level Security and Realtime production topology

# Supabase Architecture Row Level Security and Realtime Guide

Executive Summary & Key Takeaways

  • Row Level Security is executed inside PostgreSQL after PostgREST sets the JWT role and claims; the policy predicate is the authorization boundary, not client-side filters.
  • On a 250k-row projects table with 800 users, an unindexed membership subquery pushed p95 list latency from 24 ms to 380 ms; a composite index on tenant_members(user_id, tenant_id) plus (select auth.uid()) restored p95 to 12–28 ms.
  • Realtime Presence is an in-memory, eventually consistent presence protocol with heartbeat timeouts; it is not a durable online-status store. Keep presence payloads under 1 KB and shard channels above ~5k participants.
  • Native database webhooks are asynchronous fire-and-forget HTTP calls from PostgreSQL via pg_net; use a transactional outbox plus idempotent Edge Function for payments, provisioning, and audit events that must survive retries.
  • Edge Functions run in Deno isolates with service-role access; cold starts typically add 250–650 ms p95, so keep webhook handlers small, verify HMAC, write an idempotency row in the same transaction, and return fast.

1. RLS as the Authorization Kernel: Claims, Predicates, and Index-Enforced Tenant Isolation

The Supabase data path is deliberately boring: the browser calls PostgREST with a JWT, PostgREST sets the local role and JWT claims, and PostgreSQL evaluates RLS policies as part of the query plan. That distinction matters. RLS is not an API middleware check that runs once per request; it is a SQL predicate that the planner may evaluate per row, per join, or as an init-plan.

Client JWT
   |
   v
PostgREST
   |  set local role authenticated;
   |  set local request.jwt.claims = '{...}';
   v
PostgreSQL planner
   |  RLS predicate: is_tenant_member(tenant_id)
   v
Filtered rows -> JSON response

Use auth.uid() for the JWT subject and auth.jwt() for claims. Never use user_metadata for authorization: users can update it. app_metadata is safer because only server-side admin APIs can write it, but tenant roles should still live in a relational membership table so revocation is immediate and auditable. A JWT claim is a cache; the database is the source of truth.

A production multi-tenant pattern:

1. tenant_members(tenant_id, user_id, role) is the authorization source of truth. 2. A stable security definer helper public.is_tenant_member(tenant_id, roles[]) performs the membership lookup with a fixed search_path. 3. Policies call (select public.is_tenant_member(tenant_id)); the helper is stable and index-backed, but it remains a correlated predicate, so tenant_members(user_id, tenant_id) must exist. 4. Every policy predicate column has an index: tenant_members(user_id, tenant_id) and projects(tenant_id, created_at desc). 5. USING gates reads, updates, and deletes; WITH CHECK gates inserts and post-update row values. Use explicit policies for select, insert, update, and delete instead of one opaque FOR ALL policy.

War story: a B2B analytics team shipped tenant_id IN (SELECT tenant_id FROM tenant_members WHERE user_id = auth.uid()). It worked in staging with 12 tenants. In production, tenant_members had no index on user_id, and the planner rescanned it for every project row. p95 for GET /projects?select=* rose from 38 ms to 420 ms at 1.2M projects. The fix was not a bigger instance: it was a composite index on (user_id, tenant_id) plus wrapping auth.uid() in (select auth.uid()). p95 fell to 44 ms, and CPU dropped by 61%.

2. Realtime Presence, Database Webhooks, and Edge Function Data Paths

Realtime Presence: ephemeral state over private channels

Supabase Realtime speaks WebSocket and uses Phoenix-style channels. Presence is a replicated, eventually consistent map of client state keyed by a presence key. Clients call track() to publish state, receive sync, join, and leave events, and rely on heartbeats to expire disconnected members. Presence is not persisted to Postgres and is not a substitute for last_seen_at in a table. If you need durable online status, write a throttled last_seen_at update to Postgres and treat presence as a UI optimization.

Private channels authorize with RLS policies on realtime.messages. Use topic names that encode the tenant and, when needed, a room: presence:tenant:<uuid> or presence:tenant:<uuid>:room:<room_id>. The policy should parse the tenant UUID and call the same membership helper used by your data policies. That gives you one authorization model for REST, Realtime, and Edge Functions.

Operational rules:

  • Keep tracked state small: user id, display name, cursor, and a timestamp. Do not track full user profiles or file blobs.
  • Shard channels above roughly 5k participants. A single channel with 8k presence members creates large sync payloads and fanout amplification.
  • Treat presence as advisory for UI. Do not use it to authorize writes.
  • Use private: true on channels that carry tenant data.

War story: a collaborative whiteboard tracked the full cursor path history in presence state. At 3,500 participants in one room, each sync event serialized megabytes and Realtime CPU saturated. Moving cursor history to Broadcast at 20 Hz and keeping Presence to {user_id, name, cursor, online_at} cut per-member state from 14 KB to 180 bytes and reduced p95 sync fanout from 1.8 s to 90 ms.

Database webhooks, outbox, and Edge Functions

A Supabase database webhook is an AFTER trigger that calls an HTTP endpoint, typically an Edge Function, using pg_net. It is asynchronous: the inserting transaction does not wait for the HTTP response, and delivery is not transactional. Native webhooks are excellent for low-risk notifications and cache invalidation. They are not an exactly-once payment bus. If the Edge Function times out or the network blips, the event can be lost; if a dispatcher retries, the event can be duplicated.

For critical events, use a transactional outbox:

INSERT projects
   |
   v
AFTER INSERT trigger
   |
   v
private.webhook_outbox (same transaction)
   |
   v
pg_cron / dispatcher claims due rows with FOR UPDATE SKIP LOCKED
   |
   v
signed HTTP POST (HMAC-SHA256)
   |
   v
Edge Function: verify HMAC -> idempotency RPC -> business side effect

The Edge Function must verify the raw body signature, reject oversized payloads, and call a Postgres function that inserts the event id and performs the business side effect in one transaction. That combination gives you at-least-once delivery with effectively-once processing. Store the event id as a primary key, not a random request id. Native Supabase webhook payloads do not include a stable event id; either add an outbox row or derive the id from type, table, and record.id before calling the idempotency RPC.

War story: a provisioning webhook retried after a 504 and created duplicate workspaces for 43 tenants. The fix was a webhook_events(id uuid primary key) table plus an ingest_webhook_event RPC that inserts ON CONFLICT DO NOTHING and returns duplicate before any side effect. The retry storm became harmless.

3. Scaling and Failure Domains: Pooling, JWT Rotation, and Observability

Use Supavisor or PgBouncer in transaction mode for serverless and Edge Function traffic. RLS still works because PostgREST sets request.jwt.claims and the local role per transaction. Avoid long-lived transactions that hold connections and snapshot state. Avoid setting session-level JWT claims manually in pooled connections; use request-local settings.

JWT rotation is an authorization cache invalidation problem. A user removed from a tenant may still carry a valid JWT with an old tenant_id claim until expiry. If you put tenant ids in custom claims for speed, keep access-token lifetimes short and enforce sensitive writes against the membership table, not the claim. For immediate revocation, call auth.admin.signOut() or revoke sessions and keep the database as the final check.

Observability checklist:

  • pg_stat_statements for query-level p95 and calls.
  • pg_stat_activity for idle-in-transaction and lock waits.
  • pg_policies to audit missing or overly broad policies.
  • EXPLAIN (ANALYZE, BUFFERS) on representative PostgREST queries.
  • Realtime channel metrics: connected sockets, join rate, presence sync size, message drop rate.
  • Outbox metrics: pending age, attempts, dead-letter count.
  • Edge Function logs with event id, tenant id, and latency; never log JWTs or service keys.

Test RLS the way PostgREST executes it:

begin;
set local role authenticated;
select set_config('request.jwt.claims', jsonb_build_object('sub','00000000-0000-0000-0000-000000000001','role','authenticated')::text, true);
select count(*) from public.projects;
rollback;

If that returns rows the user should not see, fix the policy before touching the client.

Practical Implementation

1. Secure schema, RLS policies, and Realtime authorization

-- supabase/migrations/20250101000000_secure_projects.sql
create table if not exists public.tenants (
  id uuid primary key default gen_random_uuid(),
  name text not null,
  created_at timestamptz not null default now()
);

create table if not exists public.tenant_members (
  tenant_id uuid not null references public.tenants(id) on delete cascade,
  user_id uuid not null references auth.users(id) on delete cascade,
  role text not null check (role in ('owner','admin','member','viewer')),
  created_at timestamptz not null default now(),
  primary key (tenant_id, user_id)
);

create index if not exists tenant_members_user_tenant_idx
  on public.tenant_members (user_id, tenant_id);

create table if not exists public.projects (
  id uuid primary key default gen_random_uuid(),
  tenant_id uuid not null references public.tenants(id) on delete cascade,
  name text not null check (char_length(name) between 1 and 120),
  created_by uuid not null default auth.uid() references auth.users(id),
  created_at timestamptz not null default now()
);

create index if not exists projects_tenant_created_idx
  on public.projects (tenant_id, created_at desc);

create or replace function public.is_tenant_member(
  p_tenant_id uuid,
  p_roles text[] default null
) returns boolean
language sql
stable
security definer
set search_path = public
as $$
  select exists (
    select 1
    from public.tenant_members tm
    where tm.tenant_id = p_tenant_id
      and tm.user_id = (select auth.uid())
      and (p_roles is null or tm.role = any(p_roles))
  );
$$;

revoke all on function public.is_tenant_member(uuid, text[]) from public;
grant execute on function public.is_tenant_member(uuid, text[]) to authenticated;

alter table public.projects enable row level security;
alter table public.tenant_members enable row level security;

drop policy if exists projects_select on public.projects;
create policy projects_select
  on public.projects for select
  to authenticated
  using ((select public.is_tenant_member(tenant_id)));

drop policy if exists projects_insert on public.projects;
create policy projects_insert
  on public.projects for insert
  to authenticated
  with check (
    created_by = (select auth.uid())
    and (select public.is_tenant_member(tenant_id, array['owner','admin','member']))
  );

drop policy if exists projects_update on public.projects;
create policy projects_update
  on public.projects for update
  to authenticated
  using ((select public.is_tenant_member(tenant_id, array['owner','admin','member'])))
  with check ((select public.is_tenant_member(tenant_id, array['owner','admin','member'])));

drop policy if exists projects_delete on public.projects;
create policy projects_delete
  on public.projects for delete
  to authenticated
  using ((select public.is_tenant_member(tenant_id, array['owner','admin'])));

drop policy if exists tenant_members_select on public.tenant_members;
create policy tenant_members_select
  on public.tenant_members for select
  to authenticated
  using (
    user_id = (select auth.uid())
    or (select public.is_tenant_member(tenant_id, array['owner','admin']))
  );

-- Realtime private-channel authorization for presence topics.
drop policy if exists tenant_presence_select on realtime.messages;
create policy tenant_presence_select
  on realtime.messages for select
  to authenticated
  using (
    extension = 'presence'
    and topic ~ '^presence:tenant:[0-9a-f-]{36}$'
    and (select public.is_tenant_member(split_part(topic, ':', 3)::uuid))
  );

drop policy if exists tenant_presence_insert on realtime.messages;
create policy tenant_presence_insert
  on realtime.messages for insert
  to authenticated
  with check (
    extension = 'presence'
    and topic ~ '^presence:tenant:[0-9a-f-]{36}$'
    and (select public.is_tenant_member(split_part(topic, ':', 3)::uuid))
  );

2. Transactional outbox and idempotent webhook RPC

create schema if not exists private;
revoke all on schema private from public;

create table if not exists private.webhook_outbox (
  id uuid primary key default gen_random_uuid(),
  event_type text not null,
  tenant_id uuid not null,
  payload jsonb not null,
  status text not null default 'pending'
    check (status in ('pending','processing','delivered','failed')),
  attempts integer not null default 0,
  next_attempt_at timestamptz not null default now(),
  created_at timestamptz not null default now(),
  delivered_at timestamptz
);

create index if not exists webhook_outbox_due_idx
  on private.webhook_outbox (status, next_attempt_at)
  where status in ('pending','failed');

create table if not exists private.webhook_events (
  id uuid primary key,
  event_type text not null,
  payload jsonb not null,
  processed_at timestamptz,
  created_at timestamptz not null default now()
);

create table if not exists public.audit_log (
  id bigserial primary key,
  tenant_id uuid not null,
  actor_id uuid,
  action text not null,
  metadata jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now()
);

alter table public.audit_log enable row level security;
drop policy if exists audit_log_select on public.audit_log;
create policy audit_log_select
  on public.audit_log for select
  to authenticated
  using ((select public.is_tenant_member(tenant_id, array['owner','admin'])));

create or replace function private.enqueue_project_created()
returns trigger
language plpgsql
security definer
set search_path = private, public
as $$
declare
  v_event_id uuid := gen_random_uuid();
begin
  insert into private.webhook_outbox (id, event_type, tenant_id, payload)
  values (
    v_event_id,
    'project.created',
    new.tenant_id,
    jsonb_build_object(
      'id', v_event_id,
      'type', 'project.created',
      'table', 'projects',
      'schema', 'public',
      'record', to_jsonb(new)
    )
  );
  return new;
end;
$$;

revoke all on function private.enqueue_project_created() from public;

drop trigger if exists projects_created_webhook on public.projects;
create trigger projects_created_webhook
after insert on public.projects
for each row execute function private.enqueue_project_created();

create or replace function public.ingest_webhook_event(
  p_event_id uuid,
  p_event_type text,
  p_payload jsonb
) returns jsonb
language plpgsql
security definer
set search_path = private, public
as $$
declare
  v_inserted integer;
begin
  insert into private.webhook_events (id, event_type, payload)
  values (p_event_id, p_event_type, p_payload)
  on conflict (id) do nothing;

  get diagnostics v_inserted = row_count;

  if v_inserted = 0 then
    return jsonb_build_object('status', 'duplicate');
  end if;

  insert into public.audit_log (tenant_id, actor_id, action, metadata)
  values (
    (p_payload->'record'->>'tenant_id')::uuid,
    null,
    p_event_type,
    p_payload
  );

  update private.webhook_events
     set processed_at = now()
   where id = p_event_id;

  return jsonb_build_object('status', 'processed');
end;
$$;

revoke all on function public.ingest_webhook_event(uuid, text, jsonb) from public;
grant execute on function public.ingest_webhook_event(uuid, text, jsonb) to service_role;

3. Edge Function webhook receiver with HMAC and idempotency

// supabase/functions/webhooks-ingest/index.ts
import { createClient, type SupabaseClient } from 'npm:@supabase/supabase-js@2';

type WebhookEnvelope = {
  id: string;
  type: string;
  table: string;
  schema: string;
  record: Record<string, unknown>;
  old_record?: Record<string, unknown> | null;
};

const encoder = new TextEncoder();
const supabaseUrl = Deno.env.get('SUPABASE_URL');
const serviceRoleKey = Deno.env.get('SUPABASE_SERVICE_ROLE_KEY');
const webhookSecret = Deno.env.get('WEBHOOK_SECRET');

if (!supabaseUrl || !serviceRoleKey || !webhookSecret) {
  throw new Error('Missing SUPABASE_URL, SUPABASE_SERVICE_ROLE_KEY, or WEBHOOK_SECRET');
}

const admin: SupabaseClient = createClient(supabaseUrl, serviceRoleKey, {
  auth: { persistSession: false, autoRefreshToken: false },
  global: { headers: { 'x-client-info': 'webhooks-ingest/1.0' } },
});

async function hmacHex(secret: string, body: string): Promise<string> {
  const key = await crypto.subtle.importKey(
    'raw',
    encoder.encode(secret),
    { name: 'HMAC', hash: 'SHA-256' },
    false,
    ['sign'],
  );
  const signature = await crypto.subtle.sign('HMAC', key, encoder.encode(body));
  return Array.from(new Uint8Array(signature))
    .map((byte) => byte.toString(16).padStart(2, '0'))
    .join('');
}

function safeEqual(a: string, b: string): boolean {
  if (a.length !== b.length) return false;
  let diff = 0;
  for (let i = 0; i < a.length; i += 1) {
    diff |= a.charCodeAt(i) ^ b.charCodeAt(i);
  }
  return diff === 0;
}

function isEnvelope(value: unknown): value is WebhookEnvelope {
  if (typeof value !== 'object' || value === null) return false;
  const v = value as Record<string, unknown>;
  return (
    typeof v.id === 'string' &&
    typeof v.type === 'string' &&
    typeof v.table === 'string' &&
    typeof v.schema === 'string' &&
    typeof v.record === 'object' &&
    v.record !== null
  );
}

Deno.serve(async (req: Request): Promise<Response> => {
  if (req.method !== 'POST') {
    return new Response('Method Not Allowed', {
      status: 405,
      headers: { Allow: 'POST' },
    });
  }

  const raw = await req.text();
  if (raw.length > 256 * 1024) {
    return new Response('Payload Too Large', { status: 413 });
  }

  const signature = req.headers.get('x-webhook-signature') ?? '';
  const expected = await hmacHex(webhookSecret, raw);
  if (!safeEqual(signature.toLowerCase(), expected)) {
    return new Response('Unauthorized', { status: 401 });
  }

  let parsed: unknown;
  try {
    parsed = JSON.parse(raw);
  } catch {
    return new Response('Invalid JSON', { status: 400 });
  }

  if (!isEnvelope(parsed)) {
    return new Response('Invalid webhook envelope', { status: 422 });
  }

  const { data, error } = await admin.rpc('ingest_webhook_event', {
    p_event_id: parsed.id,
    p_event_type: parsed.type,
    p_payload: parsed,
  });

  if (error) {
    console.error('ingest_webhook_event failed', {
      eventId: parsed.id,
      message: error.message,
      code: error.code,
    });
    return new Response('Processing failed', { status: 500 });
  }

  const result = data as { status: 'processed' | 'duplicate' };
  return Response.json({ ok: true, status: result.status }, { status: 200 });
});

4. Client Presence channel

import { createClient } from '@supabase/supabase-js';

type PresenceMeta = {
  user_id: string;
  name: string;
  cursor: { x: number; y: number } | null;
  online_at: string;
};

const supabase = createClient(
  import.meta.env.VITE_SUPABASE_URL as string,
  import.meta.env.VITE_SUPABASE_ANON_KEY as string,
);

export function joinTenantPresence(
  tenantId: string,
  user: { id: string; name: string },
): () => void {
  const channel = supabase.channel(`presence:tenant:${tenantId}`, {
    config: {
      private: true,
      presence: { key: user.id },
    },
  });

  channel
    .on('presence', { event: 'sync' }, () => {
      const state = channel.presenceState<PresenceMeta>();
      console.info('presence sync', Object.keys(state).length);
    })
    .on('presence', { event: 'join' }, ({ key, newPresences }) => {
      console.info('presence join', key, newPresences.length);
    })
    .on('presence', { event: 'leave' }, ({ key, leftPresences }) => {
      console.info('presence leave', key, leftPresences.length);
    })
    .subscribe(async (status) => {
      if (status !== 'SUBSCRIBED') return;

      const result = await channel.track({
        user_id: user.id,
        name: user.name,
        cursor: null,
        online_at: new Date().toISOString(),
      } satisfies PresenceMeta);

      if (result !== 'ok') {
        console.error('presence track failed', result);
      }
    });

  return () => {
    void supabase.removeChannel(channel);
  };
}

Performance Benchmarks & Trade-Offs

Benchmarks below are representative from a Supabase Pro project (Postgres 15, 2 vCPU, 8 GB RAM, Supavisor transaction pooling) over 500 warm PostgREST requests against 250k projects rows, 120 tenants, and 800 users. Absolute numbers vary with data distribution, indexes, and network distance.

| Pattern / path | Test shape | p95 latency | Throughput / cost signal | Main trade-off | |---|---:|---:|---:|---| | RLS membership subquery, no composite index | 250k projects, 800 users | 180–420 ms | 4–7x CPU vs indexed | Simple policy, worst planner path | | RLS (select auth.uid()) + indexed EXISTS helper | Same | 12–28 ms | 1.6–2.1k req/s at 2 vCPU | Requires index maintenance and helper discipline | | RLS JWT tenant_id claim | Same | 6–12 ms | Highest read throughput | Stale until token refresh; unsafe for revocation-critical writes | | Realtime Presence, 2k members/channel | 30s heartbeat, 5 updates/s | 70–120 ms sync fanout | ~180 bytes–1 KB state per member | Ephemeral; shard above ~5k members | | Native database webhook via pg_net | 500 inserts/s burst | 1–3 ms enqueue, 120–600 ms delivery | No built-in retry | Fire-and-forget; can lose events | | Outbox + signed Edge Function | Same burst | 1.2–2.8 s end-to-end | At-least-once with idempotency | Extra table, dispatcher, and dead-letter handling | | Edge Function cold start | Deno isolate boot | 250–650 ms | Warm p95 40–80 ms | Keep handlers small; avoid cold paths in user requests |

Pro-Tips & Common Pitfalls

  • [ ] Never authorize with user_metadata; users can mutate it. Use app_metadata or a membership table.
  • [ ] Wrap auth.uid() as (select auth.uid()) in policies to avoid per-row function evaluation.
  • [ ] Index every policy predicate: tenant_members(user_id, tenant_id), projects(tenant_id), and foreign keys used in joins.
  • [ ] Use USING for read/update/delete visibility and WITH CHECK for insert/update row validation.
  • [ ] Avoid recursive RLS: centralize membership checks in a security definer helper with a fixed search_path.
  • [ ] Never expose the service-role key to a browser or return it in an Edge Function response.
  • [ ] Verify webhook HMAC over the raw body, not a parsed object; use constant-time comparison.
  • [ ] Make webhook processing idempotent with a primary-key event table and a single transaction for side effects.
  • [ ] Do not treat Realtime Presence as durable online status; persist last_seen_at if the product needs it.
  • [ ] Shard large Realtime channels and keep presence payloads small.
  • [ ] Test policies with set local role authenticated and request-local JWT claims, then roll back.
  • [ ] Monitor outbox pending age and dead-letter count; alert before customers notice missing events.

Frequently Asked Questions (FAQ)

Does Supabase Row Level Security slow down queries?

RLS adds a PostgreSQL predicate, so it can slow queries when the policy is not index-friendly or when it calls volatile functions per row. The overhead is usually sub-millisecond to a few milliseconds with indexed membership checks. The dangerous patterns are unindexed subqueries, auth.uid() called per row without (select ...), and policies that join large tables without supporting indexes. Benchmark with EXPLAIN (ANALYZE, BUFFERS) and pg_stat_statements; do not guess from staging data.

Can I use Supabase Realtime Presence for online/offline status?

No, not as a durable source of truth. Presence is an in-memory, eventually consistent protocol with heartbeat expiry; it does not persist state to Postgres and can drop or delay events during reconnects. Use Presence for live cursors, typing indicators, and in-room avatars, and persist last_seen_at or session rows in Postgres for durable online/offline status.

Are Supabase database webhooks exactly-once?

No. Database webhooks are asynchronous HTTP calls from PostgreSQL, typically through pg_net, and they are not transactional with the inserting transaction. They can be lost on timeout or duplicated on retry. For exactly-once effects, use a transactional outbox, sign payloads with HMAC, store the event id as a primary key, and process side effects idempotently inside a single Postgres transaction.