Here is a comprehensive technical blueprint for implementing a multi-tenant data architecture tailored for 10,000 mid-market B2B organizations using PostgreSQL and FastAPI. 1. Architecture Decision: Shared Schema with Row-Level Security (RLS) For a target scale of 10,000 mid-market tenants, the optimal architecture is a Shared Database, Shared Schema utilizing PostgreSQL Row-Level Security (RLS). Justification: Against Isolated Databases: Managing 10,000 PostgreSQL instances or databases is operationally catastrophic and cost-prohibitive. Connection pooling (e.g., PgBouncer) becomes a nightmare. Against Separate Schemas (Schema-per-tenant): While often recommended for 100-1,000 tenants, reaching 10,000 schemas in a single PostgreSQL database causes severe catalog bloat (pg_catalog tables become massive). Furthermore, running a single database migration requires iterating 10,000 times, turning a 1-minute deployment into a multi-hour outage risk. Why RLS: RLS allows all tenants to live in the same tables. Migration is instantaneous across all tenants. Isolation is strictly enforced at the PostgreSQL engine level, meaning even if the application layer has a bug in a WHERE clause, the database will refuse to return cross-tenant data. 2. Database Schema: RLS Implementation The fundamental mechanism is setting a session-level configuration variable (app.current_tenant_id) when a request begins. The RLS policies will read this variable to filter data. SQL -- 1. Create a dedicated application role (NEVER use postgres superuser for the app) CREATE ROLE app_user WITH LOGIN PASSWORD 'secure_password'; -- 2. Base Tenant Table (Global, typically bypassing RLS or readable by all) CREATE TABLE tenants ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), name VARCHAR(255) NOT NULL, created_at TIMESTAMPTZ DEFAULT NOW() ); -- 3. Example Domain Table: Projects CREATE TABLE projects ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE, name VARCHAR(255) NOT NULL, status VARCHAR(50) DEFAULT 'active', created_at TIMESTAMPTZ DEFAULT NOW() ); -- CRITICAL: Index the tenant_id to prevent full table scans on large tables CREATE INDEX idx_projects_tenant_id ON projects(tenant_id); -- 4. Enable Row Level Security ALTER TABLE projects ENABLE ROW LEVEL SECURITY; -- 5. Force RLS (Ensures table owners also respect RLS, a vital fallback) ALTER TABLE projects FORCE ROW LEVEL SECURITY; -- 6. Define the RLS Policies -- We cast the current_setting to UUID. If the setting is missing, it returns NULL, -- causing the policy to evaluate to false (safe failure). CREATE POLICY tenant_isolation_policy ON projects AS RESTRICTIVE FOR ALL TO app_user USING (tenant_id = NULLIF(current_setting('app.current_tenant_id', TRUE), '')::uuid) WITH CHECK (tenant_id = NULLIF(current_setting('app.current_tenant_id', TRUE), '')::uuid); -- 7. Grant Permissions to App User GRANT SELECT, INSERT, UPDATE, DELETE ON projects TO app_user; GRANT SELECT ON tenants TO app_user; 3. Backend Implementation: FastAPI & SQLAlchemy 2.0 To safely implement this in FastAPI, we use Dependency Injection to extract the tenant_id from the JWT and inject it directly into the SQLAlchemy database session. We must use SET LOCAL so the variable only lasts for the duration of the current database transaction. This prevents data leakage when connections are returned to the pool. Python from fastapi import FastAPI, Depends, HTTPException, Security from fastapi.security import HTTPBearer, HTTPAuthorizationCredentials from sqlalchemy.ext.asyncio import create_async_engine, AsyncSession, async_sessionmaker from sqlalchemy import text import jwt app = FastAPI() security = HTTPBearer() # Engine setup: connect using the restricted 'app_user' role engine = create_async_engine("postgresql+asyncpg://app_user:secure_password@db:5432/saas_db") AsyncSessionLocal = async_sessionmaker(engine, class_=AsyncSession, expire_on_commit=False) # 1. Auth Dependency to extract tenant_id securely async def get_current_tenant_id(credentials: HTTPAuthorizationCredentials = Security(security)) -> str: try: # In production, verify the signature with your public key/secret token = credentials.credentials payload = jwt.decode(token, "SECRET_KEY", algorithms=["HS256"]) tenant_id = payload.get("tenant_id") if not tenant_id: raise HTTPException(status_code=403, detail="Tenant ID missing from token") return tenant_id except jwt.PyJWTError: raise HTTPException(status_code=401, detail="Invalid authentication credentials") # 2. Database Dependency with Contextual RLS Enforcement async def get_db_session(tenant_id: str = Depends(get_current_tenant_id)) -> AsyncSession: async with AsyncSessionLocal() as session: async with session.begin(): # CRITICAL: SET LOCAL scopes the variable to the current transaction. # If the transaction ends/rolls back, the variable is cleared. await session.execute( text("SET LOCAL app.current_tenant_id = :tenant_id"), {"tenant_id": tenant_id} ) # Yield the session to the route handler yield session # The session.begin() context manager automatically commits (or rollbacks on exception). # Upon exiting, the connection is returned clean to the pool. # 3. Route Usage Example @app.get("/api/v1/projects") async def list_projects(db: AsyncSession = Depends(get_db_session)): # The developer does NOT need to write `.filter(Project.tenant_id == ...)` # The database RLS policy will automatically filter the rows. result = await db.execute(text("SELECT id, name, status FROM projects")) return {"projects": [row._asdict() for row in result]} 4. Security Fallbacks & Fail-Safes Relying on a single point of failure is dangerous. We implement three layers of fail-safes to guarantee cross-tenant data leaks cannot occur. Fail-Safe 1: SQLAlchemy Event Listeners (Insert Enforcement) If a developer forgets to set the tenant_id on a new record, the DB will reject it due to the RLS WITH CHECK clause. However, we can enforce this at the ORM layer to ensure tenant_id is automatically populated, preventing human error in the application code. Python from sqlalchemy import event from sqlalchemy.orm import Mapper def validate_tenant_id(mapper: Mapper, connection, target): # Retrieve the tenant_id from the current execution context current_tenant = connection.execute(text("SELECT current_setting('app.current_tenant_id', TRUE)")).scalar() if not current_tenant: raise Exception("Fatal: Attempted to insert data without an active tenant context.") # Auto-assign or validate if hasattr(target, 'tenant_id'): if target.tenant_id is None: target.tenant_id = current_tenant elif str(target.tenant_id) != current_tenant: raise Exception("Fatal: Attempted to insert data for a different tenant.") # Attach to the declarative base (assuming Base is your SQLAlchemy DeclarativeBase) event.listen(Base, 'before_insert', validate_tenant_id, propagate=True) Fail-Safe 2: Connection Role Principle of Least Privilege The FastAPI application connects to the database as app_user. The app_user role must not have the BYPASSRLS or SUPERUSER attributes. If the FastAPI application fails to set the app.current_tenant_id via SET LOCAL, the PostgreSQL function current_setting('app.current_tenant_id', TRUE) will evaluate to NULL. The RLS policy tenant_id = NULL will evaluate to false, resulting in zero rows returned rather than all rows. It fails closed. Fail-Safe 3: SET LOCAL vs SET Using SET app.current_tenant_id = '...' applies to the entire database connection session. Because FastAPI uses connection pooling (via SQLAlchemy/asyncpg), that connection is eventually returned to the pool and reused by another user. If not cleared, User B could read User A's data. By strictly using SET LOCAL, the configuration variable is definitively destroyed the moment the COMMIT or ROLLBACK occurs at the end of the HTTP request lifecycle, completely eliminating connection-pool data leakage.