I'm building a multi-tenant SaaS with FastAPI + Supabase (Auth + DB). Currently using supabase-py for everything, but I'm planning to migrate data access to SQLAlchemy 2.0 async + asyncpg. Wanted to check if anyone's done this before I commit.
Why I'm migrating:
The core issue is that supabase-py wraps GoTrue + PostgREST into a single stateful client. In a browser, that's fine, one user at a time. On a multi-tenant server handling concurrent requests, I'm fighting singleton corruption risks and writing ContextVar wrappers to force a stateful library to behave statelessly. Every DB request also goes through an unnecessary HTTP hop (App → PostgREST → Postgres) instead of a direct TCP connection.
The plan:
Move the entire data access layer to SQLAlchemy async + asyncpg (direct TCP to Postgres via service_role). Keep supabase-py only for Auth API calls (admin user management, auth.get_user(), etc.). Move all authorization/tenancy logic into the FastAPI service layer instead of relying on RLS.
Questions for anyone running this stack:
prepared_statement_cache_size=0 in the asyncpg config enough, or are there other gotchas? https://github.com/supabase/supabase/issues/39227filter: clinic_id=eq.${clinicId}. Realtime uses RLS SELECT policies to filter events. Has anyone removed RLS while keeping Realtime working?The user is migrating a multi-tenant SaaS application from using supabase-py for data access to SQLAlchemy 2.0 with asyncpg for direct database connections. They plan to retain supabase-py for authentication purposes. They seek advice on connection pooling, app-layer authentication, and maintaining Realtime functionality without RLS.