Supabase Client & RLS Policies

Relevant source files

The following files were used as context for generating this wiki page:

• supabase/config.toml
• supabase/migrations/20260503011925_event_phase1.sql
• supabase/migrations/20260515043737_places_cache_schema.sql
• supabase/migrations/_archive-not-on-remote/20260516120000_rls_audit_fixes.sql
• supabase/migrations/20260512120000_sponsor_schema_edge_acl.sql
• src/lib/supabase/database.types.ts
• src/mastra/tools/search-attractions.ts

Introduction

The Supabase Client and Row Level Security (RLS) policies form the core security and data access layer for the mdeai platform. This system ensures that data interactions—whether from the frontend, edge functions, or AI agents—are strictly governed by the user's identity and role. The architecture utilizes Supabase's built-in Auth and PostgREST features to expose a "Data API" that is both performant and secure.

The implementation follows a "lockdown" philosophy where RLS is enabled on every table in the public and sponsor schemas. Access is granted through granular policies tailored to specific roles such as anon, authenticated, and service_role. This structure supports complex multi-tenant requirements, such as allowing sponsors to view only their own ROI metrics while permitting public read access to active event listings.

Sources: supabase/migrations/20260503011925_event_phase1.sql:27-31, supabase/migrations/20260512120000_sponsor_schema_edge_acl.sql:45-47

Client Configuration & API Setup

The local Supabase environment is configured via supabase/config.toml. It exposes the public and graphql_public schemas by default, with an extra search path including extensions. The API is restricted to a maximum of 1000 rows per request to prevent accidental or malicious payload sizes.

API and Database Settings

Component	Configuration	Description
API Port	54321	Local endpoint for PostgREST and Auth.
Database Port	54322	Local PostgreSQL instance port.
Max Rows	1000	Limit on rows returned from any view, table, or procedure.
Pool Mode	transaction	Connection pooling mode for the database.
JWT Expiry	3600s	Duration for which authentication tokens remain valid.

Sources: supabase/config.toml:13-22, supabase/config.toml:39-44, supabase/config.toml:142-148

Authentication & Authorization Flow

The project uses a standard Supabase Auth flow where users sign up and receive a JWT. This JWT is then passed in the Authorization header to the Data API. PostgreSQL evaluates this token using the auth.uid() and auth.role() functions within RLS policies.

Client Implementation in Tools

AI agents and tools, such as the attractions search tool, instantiate the Supabase client using environment variables (SUPABASE_URL, SUPABASE_ANON_KEY). These clients typically disable session persistence for server-side execution.

The diagram shows how tools utilize the anon key and RLS-protected endpoints to fetch data.
Sources: src/mastra/tools/search-attractions.ts:57-65, src/mastra/tools/search-attractions.ts:140-145

Row Level Security (RLS) Architecture

The project enforces a strict "No FOR ALL" convention for public tables, requiring four separate policies (SELECT, INSERT, UPDATE, DELETE) per table to comply with security standards.

Key RLS Patterns

1. Ownership Check: USING (user_id = auth.uid()) ensures users only interact with their own records, such as saved places or bookings.
2. Role Pruning: Policies are scoped to specific roles (TO authenticated) to prevent unnecessary CPU usage on anonymous requests.
3. Service Role Bypass: A service_role policy USING (true) allows administrative scripts and edge functions to bypass restrictions.
4. Public Visibility: Tables like tourist_destinations or event_tickets use conditional logic to allow public read access only when a record is marked as active or published.

RLS Implementation Table

Table	Scoped Role	Policy Logic	Source
saved_places	authenticated	user_id = auth.uid()	20260524024110_restore_post_mvp_saved_places_bookings.sql:33-39
sponsor.organizations	authenticated	owner_user_id = auth.uid()	20260512120000_sponsor_schema_edge_acl.sql:49-53
places_search_cache	service_role	USING (true) (Internal only)	20260515043737_places_cache_schema.sql:54-68
outbound_clicks	authenticated	INSERT check: (select auth.uid()) = user_id	_archive-not-on-remote/20260516120000_rls_audit_fixes.sql:24-34
event_tickets	anon	is_active = true AND event.status is 'live'	20260503011925_event_phase1.sql:220-224

Sources: supabase/migrations/20260515043737_places_cache_schema.sql:22-24, supabase/migrations/_archive-not-on-remote/20260516120000_rls_audit_fixes.sql:53-61

Security hardening and RPCs

To facilitate complex operations that RLS cannot handle alone (e.g., cross-table atomic updates), the project employs SECURITY DEFINER functions. These functions run with the privileges of the creator (usually a superuser) but are strictly Revoked from PUBLIC and Granted only to necessary roles.

Atomic Operations Flow

This diagram illustrates the atomic checkout process used to prevent overselling of tickets.
Sources: supabase/migrations/20260503011925_event_phase1.sql:410-440

Critical Security RPCs

• get_anonymous_order: Allows anonymous users to fetch ticket details using a unique access_token without requiring a full account.
• ticket_validate_consume: Used by door staff to atomically check and mark a QR token as used.
• record_check_in: Internal auditing tool that records scan attempts (success or failure) into the event_check_ins table.

Sources: supabase/migrations/20260503011925_event_phase1.sql:383-395, supabase/migrations/20260503011925_event_phase1.sql:570-585

Database Types and Integration

The project maintains a strongly typed interface via src/lib/supabase/database.types.ts. This file is generated from the remote schema and provides TypeScript definitions for all 122+ tables, views, and enums.

export type Database = {
  public: {
    Tables: {
      ai_runs: {
        Row: {
          id: string
          agent_name: string
          status: Database["public"]["Enums"]["ai_run_status"] | null
          user_id: string | null
          // ...
        }
        // ...
      }
    }
  }
}

Sources: src/lib/supabase/database.types.ts:1-35

Conclusion

The combination of a well-configured Supabase Client and a robust RLS policy framework allows the mdeapp to scale its AI-driven discovery features while maintaining data integrity. By leveraging SECURITY DEFINER RPCs for atomic operations and strictly controlling role-based access to both standard and cached data, the system effectively mitigates common vulnerabilities like overselling or unauthorized data exposure.