Row Level Security

Row Level Security (RLS) is enabled on every table in Client Portal. RLS policies enforce authorization at the database level, meaning even if a user crafts a custom query, they can only access data they are authorized to see.

How RLS Works

1. The Supabase client sends the user's JWT with every request.
2. PostgreSQL extracts the user ID via auth.uid().
3. RLS policies evaluate against the user's roles and client assignment.
4. Only rows matching the policy are returned (for SELECT) or allowed (for INSERT/UPDATE/DELETE).

Security Model

The Supabase anon key is embedded in the frontend bundle and is public. Security relies entirely on RLS policies. Without RLS, anyone with the anon key could read all data.

Helper Functions

Three SQL functions are used across all RLS policies. All are SECURITY DEFINER to bypass RLS on the tables they read from.

has_role(role text)

Returns true if the current authenticated user has the specified role in user_roles.

CREATE OR REPLACE FUNCTION has_role(role text)
RETURNS boolean AS $$
  SELECT EXISTS (
    SELECT 1 FROM user_roles
    WHERE user_id = auth.uid()
      AND role = $1
  );
$$ LANGUAGE sql SECURITY DEFINER STABLE;

get_user_client_id()

Returns the client_id from user_profiles for the current user. Returns NULL for internal users (admin/bdr).

CREATE OR REPLACE FUNCTION get_user_client_id()
RETURNS uuid AS $$
  SELECT client_id FROM user_profiles
  WHERE user_id = auth.uid();
$$ LANGUAGE sql SECURITY DEFINER STABLE;

is_internal_user()

Returns true if the current user has the admin or bdr role.

CREATE OR REPLACE FUNCTION is_internal_user()
RETURNS boolean AS $$
  SELECT EXISTS (
    SELECT 1 FROM user_roles
    WHERE user_id = auth.uid()
      AND role IN ('admin', 'bdr')
  );
$$ LANGUAGE sql SECURITY DEFINER STABLE;

Policy Patterns

Pattern 1: Admin Full Access

Admins can see and modify all data. This is the baseline for every table.

-- SELECT: Admin sees everything
CREATE POLICY "admin_select_all" ON clients
  FOR SELECT USING (has_role('admin'));

-- INSERT/UPDATE/DELETE: Admin can modify everything
CREATE POLICY "admin_all" ON clients
  FOR ALL USING (has_role('admin'));

Pattern 2: Internal Users (Admin + BDR)

BDRs have read access to most tables. For some tables (like sales), they also have write access.

CREATE POLICY "internal_select" ON campaigns
  FOR SELECT USING (
    has_role('admin') OR has_role('bdr')
  );

Pattern 3: Client Data Isolation

Clients can only see rows that belong to their client_id.

CREATE POLICY "clients_select" ON clients
  FOR SELECT USING (
    has_role('admin') OR has_role('bdr')
    OR id = get_user_client_id()
  );

Pattern 4: Campaign/Contact Scoping

Data that belongs to a client via client_id column:

CREATE POLICY "campaigns_select" ON campaigns
  FOR SELECT USING (
    has_role('admin') OR has_role('bdr')
    OR client_id = get_user_client_id()
  );

Pattern 5: Sales Tables (Internal Only)

Sales tables are only accessible to internal users:

CREATE POLICY "sales_prospects_select" ON sales_prospects
  FOR SELECT USING (is_internal_user());

CREATE POLICY "sales_prospects_insert" ON sales_prospects
  FOR INSERT WITH CHECK (is_internal_user());

CREATE POLICY "sales_prospects_update" ON sales_prospects
  FOR UPDATE USING (is_internal_user());

CREATE POLICY "sales_prospects_delete" ON sales_prospects
  FOR DELETE USING (has_role('admin'));

Pattern 6: Booking Links (Dual Owner)

Booking links can be owned by either a client or a user:

CREATE POLICY "booking_links_select" ON booking_links
  FOR SELECT USING (
    has_role('admin') OR has_role('bdr')
    OR client_id = get_user_client_id()
    OR user_id = auth.uid()
  );

Full Policy Matrix

Table	Admin	BDR	Client
clients	CRUD	Read	Read (own)
user_profiles	CRUD	Read	Read/Update (own)
user_roles	CRUD	Read	Read (own)
campaigns	CRUD	Read/Update	Read (own client_id)
contacts	CRUD	Read/Update	Read (own client_id)
calls	CRUD	CRUD	Read (own client_id)
meetings	CRUD	CRUD	CRUD (own client_id)
markets	CRUD	Read	Read (own client_id)
signals	CRUD	Read	Read (own client_id)
attorneys	CRUD	Read	None
firms	CRUD	Read	None
sales_prospects	CRUD	CRUD	None
sales_calls	CRUD	CRUD	None
sales_meetings	CRUD	CRUD	None
sales_campaigns	CRUD	Read	None
booking_links	CRUD	CRUD	CRUD (own)
calendar_connections	CRUD	Read	CRUD (own client_id)
user_calendar_connections	CRUD	CRUD (own user_id)	None
ghl_integrations	CRUD	Read	None
ghl_import_staging	CRUD	Read	None
ghl_import_batches	CRUD	Read	None
notification_preferences	CRUD	CRUD (own)	CRUD (own)

Edge Function Bypass

Edge functions that need to bypass RLS use the service role key to create an admin Supabase client:

import { createClient } from 'https://esm.sh/@supabase/supabase-js@2';

const supabaseAdmin = createClient(
  Deno.env.get('SUPABASE_URL')!,
  Deno.env.get('SUPABASE_SERVICE_ROLE_KEY')!
);

// This client bypasses all RLS policies
const { data } = await supabaseAdmin.from('sales_calls').insert(record);

Service Role Key

The SUPABASE_SERVICE_ROLE_KEY bypasses all RLS. It is stored as a Supabase secret and is only available inside edge functions. It must never be exposed to the frontend.

Testing RLS Policies

To test RLS policies locally, use the Supabase SQL editor with SET commands:

-- Simulate a specific user
SET request.jwt.claim.sub = 'user-uuid-here';

-- Test a query
SELECT * FROM campaigns;

-- Reset
RESET request.jwt.claim.sub;