Pattern: Supabase Flask Integration

A server-side pattern for connecting a Flask application to Supabase using the Python client, with RLS as the authorization layer.

Pattern Structure

Context: A Python Flask app needs to read from a Supabase-hosted PostgreSQL database without managing raw database connections.

Forces:

• Credentials must not be hardcoded or exposed
• Authorization should be enforced at the database layer, not just the application layer
• The Supabase client should not be re-instantiated on every request

Solution:

1. Module-level singleton — call create_client() once at import time, bind to a typed variable:

   ```python supabase: Client = create_client( os.environ.get("SUPABASE_URL"), os.environ.get("SUPABASE_PUBLISHABLE_KEY") ) ```

1. Environment-based config — load credentials from .env via python-dotenv; never commit secrets

1. Method-chain query API — all queries follow .table(name).select(cols).execute(), with .data holding results:

   ```python response = supabase.table('instruments').select("*").execute() rows = response.data ```

1. RLS as the authorization layer — grant minimum permissions to the anon role and enforce access via Row Level Security policies; app code carries no auth logic for public reads

Consequences

• Clean separation: Flask handles routing/rendering; Supabase handles data + auth
• RLS failures are silent from the app perspective (returns empty result, not an error) — test policies explicitly
• The publishable key is appropriate for server-side use; use the service key only when RLS must be bypassed (admin operations)

Related Patterns

• lit-supabase-flask-quickstart — source literature note
• dotnet-agent-integration — comparable pattern in .NET context