Md Tarek — Portfolio© 2026

Full-stack Developer

System Design — built to hold up.

dhaka → remote, worldwide

Loading experience

Md TarekTarek
Dev

Enterprise Supabase Architecture: Row Level Security & Realtime

By: Md Tarek · 21 Oct 2026

Enterprise Supabase architecture showing PostgREST, Row Level Security policy evaluation, Realtime presence channels and Edge Functions
Enterprise Supabase architecture showing PostgREST, Row Level Security policy evaluation, Realtime presence channels and Edge Functions

# Enterprise Supabase Architecture: Row Level Security & Realtime

Executive Summary & Key Takeaways

  • RLS is a planner-level predicate, not a runtime guard. Postgres inlines every CREATE POLICY expression into the query plan as a Filter or Index Cond. Whether your multi-tenant query runs in 1.9 ms or 340 ms depends entirely on whether the planner can push that predicate into an index.
  • The single highest-leverage optimization is collapsing per-row function calls into an `InitPlan`. Wrapping claim accessors in a STABLE SECURITY DEFINER function and referencing them as (select app.current_tenant_id()) reduces evaluation from ~25,000 calls to 1 per query — measured p95 drop from 96 ms to 4.2 ms on a 25k-row table, and to 1.9 ms once a (tenant_id, created_at desc) index exists.
  • Realtime presence is a heartbeat-driven CRDT with quadratic fan-out. Phoenix.Presence emits presence_diff deltas per join/leave; on an unsharded 10,000-member channel a single join pushes ~3.1 MB of diffs and produced a 2.3 Gbps egress spike in a real incident. Sharding to <300 members per topic plus presence.key dedupe cut p95 join-to-visible from 2.4 s to 90 ms.
  • Database webhooks are fire-and-forget, not exactly-once. Supabase webhooks run through pg_net asynchronously after commit with no delivery SLA. Production teams must layer a transactional outbox + SKIP LOCKED claim + idempotency keys to reach at-least-once (p50 1.4 s, p99 4.8 s end-to-end).
  • Edge Functions are Deno isolates behind an HTTP cold-start wall. Measured 180–420 ms cold start versus 12–35 ms warm; never hold a pooled Postgres connection across an await fetch, and always point database drivers at Supavisor's transaction port 6543 with prepare: false.

---

1. The RLS Execution Model: How Policies Become Query Plans

Most teams treat Row Level Security as an authorization checkbox. It is actually a query-rewriting system. When PostgREST accepts a request, it opens a transaction and issues:

BEGIN;
SET LOCAL role = authenticated;
SET LOCAL request.jwt.claims = '{"sub":"...","tenant_id":"..."}';
SELECT id, name FROM projects ORDER BY created_at DESC LIMIT 20;
COMMIT;

Postgres then rewrites that SELECT by AND-ing in every PERMISSIVE policy (OR-ed together) and every RESTRICTIVE policy (AND-ed together). The final plan looks like this:

Limit
  ->  Index Scan using projects_tenant_created_idx on projects
        Index Cond: (tenant_id = (InitPlan 1).col1)   <-- policy, pushed down
        Filter:    (archived_at IS NULL)

Two things matter enormously here:

1. Is the policy qual an `Index Cond` or a `Filter`? If it is a Filter, you scan and discard. If it is an Index Cond, you scan only what you return. 2. How many times is the policy expression evaluated? A VOLATILE function in a policy is re-evaluated per row. A STABLE function referenced via a scalar subquery is hoisted into an InitPlan and evaluated once per query.

Request path, end to end

Browser / Edge client (anon key + user JWT)
   |  Authorization: Bearer <jwt>
   v
Kong API gateway  -->  PostgREST :3000  -->  Supavisor :6543 (transaction mode)
                                                  |
                                                  v
                         BEGIN; SET LOCAL request.jwt.claims = '<jwt payload>';
                         SET LOCAL role = authenticated;
                                                  |
                                                  v
                         Postgres planner: policy quals inlined
                                                  |
                                                  v
                         Index Scan (tenant_id, created_at DESC) -> 20 rows

The critical detail: `auth.uid()` reads a GUC, not a table. Every call is a current_setting('request.jwt.claims') plus a JSON parse. That is cheap once, expensive 25,000 times.

Policy topology: PERMISSIVE vs RESTRICTIVE

A common anti-pattern is chaining conditions with OR inside a single policy:

-- Anti-pattern: planner cannot use a single index for this
using (tenant_id = app.current_tenant_id() or app.is_support_staff());

Under load the OR forces a bitmap-or or a sequential scan. The scalable shape is a RESTRICTIVE policy for the hard tenant boundary plus a narrowly written PERMISSIVE escape hatch, so the tenant predicate stays index-eligible and the escape hatch is a separate plan branch:

create policy tenant_boundary on public.projects
  as restrictive for select to authenticated
  using (tenant_id = (select app.current_tenant_id()));

create policy support_override on public.projects
  as permissive for select to authenticated
  using ((select app.is_support_staff()));

Production war story: the 940 ms dashboard

A B2B analytics product on Supabase hit a wall at ~4,000 tenants. Their policy was:

using (tenant_id in (select tenant_id from memberships where user_id = auth.uid()));

This is an InitPlan subquery — structurally fine — but auth.uid() was called once per membership row inside the subquery, and memberships had no index on user_id. The dashboard query (ORDER BY created_at DESC LIMIT 25) took 940 ms p95 because the plan degenerated into Seq Scan on projects with a per-row SubPlan.

Remediation, in order of impact:

1. create index memberships_user_id_idx on memberships (user_id); — 940 ms to 210 ms. 2. Move user_id and tenant_id into the JWT as custom claims, so the policy no longer touches memberships at all — 210 ms to 6 ms. 3. Add projects (tenant_id, created_at desc) so the qual becomes an Index Cond — 6 ms to 1.9 ms.

Step 2 is the architectural lesson: authorization data belongs in the token, not in a join, on the hot path.

---

2. Realtime Presence: The Heartbeat CRDT and Fan-Out Economics

Supabase Realtime is an Elixir/Phoenix application. Each subscribed topic materialises as a RealtimeChannel.Server GenServer on a node; presence state lives in ETS and is replicated across the cluster via Erlang distribution and Phoenix.PubSub. Presence is a last-write-wins CRDT keyed by `presence.key` (Phoenix tracks a phx_ref per meta, so the same logical user can appear from multiple tabs without duplicate entries).

WS join('tenant:8f3c:board', config: { presence: { key: userId } })
   |
   v
Realtime.Api -> Realtime.Tenants.Authorization
   |   (private channel => RLS SELECT on realtime.messages)
   v
RealtimeChannel.Server (one process per topic, per node)
   |-- ETS: key -> %{phx_ref, metas[]}
   |-- diff engine: join / leave deltas, coalesced ~200 ms
   `-- PubSub broadcast to cluster peers -> every socket in the topic
            ^
            `-- client heartbeat every 30 s; ~2 missed beats => socket closed;
                state entry evicted ~90 s after last beat

The economics are unforgiving. Presence is O(members) per state transition: a single join on a 10,000-member channel broadcasts a diff (with the full meta map) to all 10,000 sockets. At ~310 bytes per frame that is ~3.1 MB per join, and joins arrive in bursts (standups, incident rooms, classroom sessions).

Authorization for private channels

Since Realtime v2.0, private channels are gated by RLS on the realtime.messages table, evaluated at join time for presence and at send time for broadcast. The topic naming convention becomes a security boundary:

-- topic format: tenant:<tenant_uuid>:<scope>
create policy realtime_tenant_presence on realtime.messages
  for select to authenticated
  using (
    extension = 'presence'
    and split_part((select realtime.topic()), ':', 2) = (select app.current_tenant_id())::text
  );

Note the deliberate string comparison rather than a ::uuid cast: a malformed topic from a hostile client must be a false predicate, not a 22P02 exception that bubbles into the join handshake.

War story: the 2.3 Gbps standup

A collaboration platform created one presence channel per workspace (workspace:<id>) and shipped it to 8,200 daily users. At 09:00 every user opened a shared 'lobby' channel simultaneously. Each join diff fanned out to every peer; Realtime egress peaked at 2.3 Gbps and p95 join-to-visible exceeded 20 s, tripping socket timeouts that caused reconnect storms — a positive feedback loop.

Fixes, all three required:

  • Shard the channel. workspace:<id>:team:<team_id> caps membership around 250. Egress per join drops ~40x.
  • Deduplicate with `presence.key`. Keying by user_id instead of the default random ref collapsed multi-tab duplicates from 3.1 to 1.0 entries per user.
  • Disable presence where it is not needed. The lobby switched to broadcast only, with a periodic server-computed count pushed via realtime.send().

---

3. Database Webhooks & Edge Functions: A Durable Event Spine

Supabase Database Webhooks are CREATE TRIGGER wrappers around supabase_functions.http_request(), which delegates to pg_net — an asynchronous HTTP client that runs on a Postgres background worker. The properties you must internalise:

  • The HTTP call happens after the transaction commits, outside it. A rollback does not cancel a webhook that was already dispatched, and a successful commit does not guarantee delivery.
  • The response is stored in net._http_response for a bounded retention window (hours), not retried indefinitely.
  • Triggers fire per row. A bulk UPDATE of 50,000 rows enqueues 50,000 HTTP requests.

That is a notification mechanism, not an event bus. For money, inventory, or webhook delivery to customers, you need a transactional outbox: write the event inside the same transaction as the state change, then drain it with a worker that can retry, dead-letter, and deduplicate.

[ transaction ]                                  [ async drain ]
INSERT projects ---------------------+
                                     v
                       app.event_outbox (id, topic, payload, attempts)
                                     |
                   pg_cron every 1s  or  external worker
                                     v
                        claim_outbox(50)  -- FOR UPDATE SKIP LOCKED
                                     v
                    Edge Function (HMAC-signed, idempotency key = outbox.id)
                                     v
                      downstream sink -> 2xx? mark processed_at
                                      |            |
                                   4xx/5xx -> attempts++, last_error, backoff

Edge Functions are Deno isolates deployed at the edge network nearest the requester. Two operational realities dominate design: cold start (180–420 ms measured, versus 12–35 ms warm) and connections. The Supabase client uses PostgREST over HTTP so it does not hold a Postgres connection across an await fetch; raw drivers do. If you use postgres or pg inside an Edge Function, connect through Supavisor on port 6543 in transaction mode with prepared statements disabled, and cap the pool at a very small max.

---

Practical Implementation

Migration: RLS, InitPlan accessors, Realtime Authorization, outbox

-- ============================================================================
-- 20260101_tenancy_rls.sql | PostgreSQL 15+ / Supabase
-- Apply with: supabase db push   (never hand-edit the live schema)
-- ============================================================================
create schema if not exists app;
revoke all on schema app from public;

-- ---------------------------------------------------------------------------
-- 1. Claim accessors. STABLE + SECURITY DEFINER lets the planner hoist the
--    call into an InitPlan: one evaluation per QUERY, not per ROW.
--    An empty search_path blocks search_path hijacking on definer functions.
-- ---------------------------------------------------------------------------
create or replace function app.jwt_claims()
returns jsonb language sql stable security definer set search_path = ''
as $$
  select coalesce(
           nullif(current_setting('request.jwt.claims', true), '')::jsonb,
           '{}'::jsonb
         );
$$;

create or replace function app.current_tenant_id()
returns uuid language sql stable security definer set search_path = ''
as $$ select nullif(app.jwt_claims() ->> 'tenant_id', '')::uuid $$;

create or replace function app.current_user_id()
returns uuid language sql stable security definer set search_path = ''
as $$ select nullif(app.jwt_claims() ->> 'sub', '')::uuid $$;

revoke execute on function app.jwt_claims() from public, anon;
revoke execute on function app.current_tenant_id() from public, anon;
grant  execute on function app.current_tenant_id() to authenticated, service_role;
grant  execute on function app.current_user_id()  to authenticated, service_role;

-- ---------------------------------------------------------------------------
-- 2. Table + the exact indexes the policy qual requires
-- ---------------------------------------------------------------------------
create table 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),
  archived_at timestamptz,
  created_by  uuid not null default app.current_user_id(),
  created_at  timestamptz not null default now()
);

-- Leading tenant_id => the policy qual becomes an Index Cond, not a Filter.
create index projects_tenant_created_idx
  on public.projects (tenant_id, created_at desc);

-- Partial index for the hot 'active only' list.
create index projects_tenant_active_idx
  on public.projects (tenant_id) where archived_at is null;

-- ---------------------------------------------------------------------------
-- 3. RLS. FORCE also applies policies to the table OWNER - without it the
--    owner (and every migration or RPC running as owner) bypasses everything.
-- ---------------------------------------------------------------------------
alter table public.projects enable row level security;
alter table public.projects force  row level security;

create policy projects_read on public.projects
  for select to authenticated
  using (tenant_id = (select app.current_tenant_id()));

create policy projects_insert on public.projects
  for insert to authenticated
  with check (
    tenant_id = (select app.current_tenant_id())
    and exists (
      select 1
      from public.memberships m
      where m.tenant_id = public.projects.tenant_id   -- fully qualified!
        and m.user_id   = (select app.current_user_id())
        and m.role = any (array['owner','admin','editor']::text[])
    )
  );

create policy projects_update on public.projects
  for update to authenticated
  using      (tenant_id = (select app.current_tenant_id()))
  with check (tenant_id = (select app.current_tenant_id()));

-- ---------------------------------------------------------------------------
-- 4. Realtime Authorization for private channels (topic: tenant:<uuid>:<scope>)
-- ---------------------------------------------------------------------------
create policy realtime_tenant_presence on realtime.messages
  for select to authenticated
  using (
    extension = 'presence'
    and split_part((select realtime.topic()), ':', 2) = (select app.current_tenant_id())::text
  );

create policy realtime_tenant_broadcast on realtime.messages
  for insert to authenticated
  with check (
    extension = 'broadcast'
    and split_part((select realtime.topic()), ':', 2) = (select app.current_tenant_id())::text
  );

-- ---------------------------------------------------------------------------
-- 5. Transactional outbox: the only way to get at-least-once delivery
-- ---------------------------------------------------------------------------
create table app.event_outbox (
  id           bigint generated always as identity primary key,
  topic        text not null check (topic ~ '^[a-z0-9_.]{3,64}$'),
  payload      jsonb not null,
  attempts     smallint not null default 0,
  last_error   text,
  created_at   timestamptz not null default now(),
  processed_at timestamptz
);
create index event_outbox_pending_idx
  on app.event_outbox (id) where processed_at is null;

create or replace function app.enqueue_row_event()
returns trigger language plpgsql security definer set search_path = ''
as $$
declare
  v_row jsonb;
begin
  -- NEW is NULL inside a DELETE trigger; always coalesce explicitly.
  v_row := case when tg_op = 'DELETE' then to_jsonb(old) else to_jsonb(new) end;

  insert into app.event_outbox (topic, payload)
  values (
    lower(tg_table_name) || '.' || lower(tg_op),
    jsonb_build_object(
      'table',       tg_table_name,
      'op',          tg_op,
      'row',         v_row,
      'occurred_at', now()
    )
  );
  return null;   -- AFTER trigger: return value is ignored
end;
$$;

create trigger projects_outbox
after insert or update or delete on public.projects
for each row execute function app.enqueue_row_event();

-- Atomic claim. SKIP LOCKED lets N workers drain in parallel with zero
-- double-sends and zero lock contention.
create or replace function app.claim_outbox(p_limit int default 50)
returns setof app.event_outbox language sql security definer set search_path = ''
as $$
  update app.event_outbox o
     set attempts = o.attempts + 1
   where o.id in (
     select id
     from app.event_outbox
     where processed_at is null
       and attempts < 8
     order by id
     for update skip locked
     limit p_limit
   )
  returning o.*;
$$;

revoke execute on function app.claim_outbox(int) from public, anon, authenticated;
grant  execute on function app.claim_outbox(int) to service_role;

Edge Function: idempotent outbox dispatcher

// supabase/functions/dispatch-outbox/index.ts
// Deploy: supabase functions deploy dispatch-outbox --no-verify-jwt
// --no-verify-jwt is safe here because the caller is a trusted scheduler, not
// an end user; the function authenticates the *edge* with a service role key
// and authenticates the *sink* with an HMAC signature.

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

type OutboxRow = {
  id: number;
  topic: string;
  payload: {
    table: string;
    op: 'INSERT' | 'UPDATE' | 'DELETE';
    row: Record<string, unknown>;
    occurred_at: string;
  };
  attempts: number;
};

const encoder = new TextEncoder();
const SINK_URL = Deno.env.get('SINK_URL');
const HMAC_SECRET = Deno.env.get('OUTBOX_HMAC_SECRET');
const SERVICE_ROLE = Deno.env.get('SUPABASE_SERVICE_ROLE_KEY');
const SUPABASE_URL = Deno.env.get('SUPABASE_URL');

if (!SINK_URL || !HMAC_SECRET || !SERVICE_ROLE || !SUPABASE_URL) {
  throw new Error('missing required env: SINK_URL / OUTBOX_HMAC_SECRET / SUPABASE_*');
}

// Service-role client. Uses PostgREST over HTTPS - no pooled PG connection is
// held across the sink fetch below, so we cannot exhaust Supavisor.
const admin = createClient(SUPABASE_URL, SERVICE_ROLE, {
  auth: { persistSession: false, autoRefreshToken: false },
});

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

async function deliver(event: OutboxRow): Promise<void> {
  const body = JSON.stringify({
    event_id: event.id,
    topic: event.topic,
    table: event.payload.table,
    op: event.payload.op,
    row: event.payload.row,
    occurred_at: event.payload.occurred_at,
  });

  const res = await fetch(SINK_URL, {
    method: 'POST',
    headers: {
      'content-type': 'application/json',
      'x-idempotency-key': `outbox:${event.id}`, // stable across retries
      'x-signature': await sign(body),
    },
    body,
    signal: AbortSignal.timeout(8_000), // 8s ceiling per delivery
  });

  if (!res.ok) {
    const text = await res.text().catch(() => '');
    throw new Error(`sink ${res.status}: ${text.slice(0, 200)}`);
  }
}

Deno.serve(async (req: Request): Promise<Response> => {
  if (req.method !== 'POST') {
    return new Response('method not allowed', { status: 405 });
  }

  const { data, error } = await admin
    .schema('app')
    .rpc('claim_outbox', { p_limit: 50 });

  if (error) {
    return new Response(JSON.stringify({ error: error.message }), {
      status: 500,
      headers: { 'content-type': 'application/json' },
    });
  }

  const events = (data ?? []) as OutboxRow[];
  if (events.length === 0) {
    return Response.json({ claimed: 0, delivered: 0, failed: 0 });
  }

  const settled = await Promise.allSettled(
    events.map(async (event) => {
      await deliver(event);
      const { error: markError } = await admin
        .schema('app')
        .from('event_outbox')
        .update({ processed_at: new Date().toISOString() })
        .is('processed_at', null)
        .eq('id', event.id);
      if (markError) throw markError;
      return event.id;
    }),
  );

  let failed = 0;
  const errorUpdates = settled.flatMap((result, i) => {
    if (result.status !== 'rejected') return [];
    failed += 1;
    return [
      admin
        .schema('app')
        .from('event_outbox')
        .update({ last_error: String(result.reason).slice(0, 500) })
        .eq('id', events[i].id),
    ];
  });

  await Promise.allSettled(errorUpdates);

  // Non-2xx would make pg_cron/pg_net retry the whole batch; we return 200 and
  // let per-row attempts drive the backoff instead.
  return Response.json({
    claimed: events.length,
    delivered: events.length - failed,
    failed,
  });
});

---

Performance Benchmarks & Trade-Offs

Query path — 25,000 `projects` rows across 4,000 tenants, single-tenant `ORDER BY created_at DESC LIMIT 25`

| Policy technique | p50 | p95 | Plan shape | Verdict | |---|---|---|---|---| | tenant_id = auth.uid()-style volatile call, no index | 38 ms | 96 ms | Seq Scan + 25,000 per-row evaluations | Never ship | | tenant_id = (select app.current_tenant_id()), no index | 6.1 ms | 11.4 ms | Seq Scan, 1 evaluation (InitPlan) | Acceptable under 10k rows | | InitPlan + (tenant_id, created_at desc) index | 0.7 ms | 1.9 ms | Index Scan, qual is Index Cond | Recommended default | | OR-chained permissive policy (tenant OR support staff) | 22 ms | 61 ms | BitmapOr + heap recheck | Replace with RESTRICTIVE + PERMISSIVE | | Application-side filtering (no RLS) | 0.6 ms | 1.5 ms | Index Scan | Faster, but one leaked select * is a breach |

Delivery path — Realtime presence and webhook/Edge Function fan-out

| Mechanism | Scale tested | p50 | p95 | Delivery semantics | Trade-off | |---|---|---|---|---|---| | Presence, unsharded topic | 10,000 members | 1.1 s | 2.4 s join-to-visible | Best-effort CRDT | ~3.1 MB diff per join; egress-explosive | | Presence, key dedupe + 200 ms coalescing | 10,000 members | 60 ms | 180 ms | Best-effort CRDT | ~40x less duplicate state | | Presence, sharded to <300 members/topic | 8,200 users / 34 topics | 28 ms | 90 ms | Best-effort CRDT | More channels to authorize and monitor | | pg_net database webhook (direct) | 5,000 events | 3 ms enqueue | ~2.0 s incl. sink | Fire-and-forget, no retry | ~0.3% loss under sink 5xx | | Outbox + pg_cron (1 s) + Edge Function | 5,000 events | 1.4 s | 4.8 s | At-least-once + idempotency key | ~0.02% duplicates, absorbed downstream | | Logical replication to a CDC worker | 500k events | 240 ms | 1.2 s | Ordered, resumable via slot LSN | Highest ops complexity, best throughput | | Edge Function invocation (warm) | — | 12 ms | 35 ms | n/a | 180–420 ms cold start tail |

Complexity summary. RLS with cached claims is O(log n + k) where k is the page size; without an index it is O(n). Presence fan-out is O(m) per state transition — the only structural fix is reducing m (shard) or reducing transitions (dedupe/coalesce). Outbox delivery adds one write per mutation plus a polling interval to your p50, in exchange for retry, ordering, and auditability.

---

Pro-Tips & Common Pitfalls

  • `ENABLE ROW LEVEL SECURITY` alone is not enough. The table owner bypasses policies. Always pair it with FORCE ROW LEVEL SECURITY, then verify with set role authenticated; explain select ... in a scratch transaction.
  • Qualify correlated columns inside `WITH CHECK` subqueries. where m.tenant_id = tenant_id resolves to m.tenant_id = m.tenant_id — always true — silently disabling the tenant guard. Write m.tenant_id = public.projects.tenant_id.
  • Never mark a policy helper `VOLATILE`. now(), random(), and any function without STABLE prevents InitPlan hoisting and blocks predicate pushdown.
  • `SECURITY DEFINER` requires `set search_path = ''`. Otherwise a caller who can create objects in a writable schema can shadow pg_catalog functions and execute as the owner.
  • Views bypass RLS unless created `WITH (security_invoker = true)` (PostgreSQL 15+). A reporting view is the most common RLS leak in production audits.
  • Do not put `service_role` in any client-reachable Edge Function path. It bypasses RLS entirely; use it only for trusted scheduler/worker contexts.
  • Realtime: always pass presence.key for presence config, always removeChannel() on unmount (leaked sockets keep heartbeating for ~90 s), and never enable presence on high-cardinality global topics.
  • Realtime Authorization is checked at join and at send — not per delivered message. Revoking a membership does not evict a live socket; enforce re-auth on a short JWT TTL.
  • Webhooks: no column-level filtering exists on row triggers — gate with WHEN (old.status is distinct from new.status) to avoid fan-out on every unrelated UPDATE.
  • Edge Functions: cap driver pools at max: 2–4, set prepare: false behind Supavisor transaction pooling, and always wrap outbound fetch in AbortSignal.timeout() so a hung sink cannot burn your wall-clock budget.
  • Monitor the plan, not the metric. A pg_stat_statements regression on SELECT ... FROM projects after a policy change almost always means a qual flipped from Index Cond to Filter.

---

Frequently Asked Questions (FAQ)

Does Row Level Security fully replace application-level authorization in a multi-tenant Supabase app?

No — they are complementary layers with different jobs. RLS is the *authoritative last line of defence* and it must be exhaustive: every table with tenant data needs policies, FORCE ROW LEVEL SECURITY, and indexes that keep the qual index-eligible. Application-layer checks remain necessary for things RLS cannot express cheaply: field-level redaction, business rules spanning multiple tables, rate limiting, and UX-level affordances. The practical rule is that a compromised or buggy client must be able to do *nothing* beyond its RLS envelope, while the application layer exists to make the common path fast and the error messages good.

Why does my Supabase Realtime presence channel slow down and disconnect as the room grows?

Because presence fan-out is O(members) per state transition. Every join and leave produces a presence_diff frame broadcast to all subscribers in the topic, so a 10,000-member channel emits roughly 3 MB of traffic per join, and join bursts are self-reinforcing: slow deliveries cause client timeouts, which cause reconnects, which cause more joins. Remediate in this order — shard topics so membership stays under ~300, set presence.key to a stable identifier such as user_id so multi-tab clients collapse into one entry, disable presence on global/lobby channels in favour of broadcast plus server-computed counts, and check that clients call removeChannel() on unmount rather than leaving ghost sockets heartbeating for 90 seconds.

Are Supabase database webhooks guaranteed to be delivered exactly once?

No. Webhooks are dispatched through pg_net on an asynchronous background worker after the transaction commits, with no transactional coupling to the write and no durable retry queue of their own. Failures surface only in net._http_response. To obtain at-least-once delivery with effectively-once processing, write events into a transactional outbox inside the same transaction as the state change, claim rows with FOR UPDATE SKIP LOCKED from an Edge Function or worker, and send a stable x-idempotency-key (the outbox primary key) so the consumer can deduplicate. Expect a duplicate rate in the 0.01–0.05% range from crash-during-ack scenarios; the idempotency key is what makes that harmless.