Multi-Tenant Data Access Patterns

The Feature

An organizer accessing their market’s vendor applications sees only their data. If a bug or misconfigured query accidentally omits the tenant filter, the organizer sees only their data. The system makes it harder to write an insecure query than a secure one.

The Decision

Tenant isolation is enforced at two layers. The application layer uses a dependency injection pattern that provides the organizer’s profile and market access as pre-validated objects. The database layer uses Supabase RLS policies (Chapter 6) as a safety net. This chapter covers the application layer.

The Implementation

Organizer Profile Dependency

# backend/app/dependencies.py (additions)
from sqlalchemy import select
from sqlalchemy.ext.asyncio import AsyncSession

from app.database import get_db
from app.models.organizer import OrganizerProfile
from app.models.market import Market


async def get_organizer_profile(
    db: AsyncSession = Depends(get_db),
    current_user: CurrentUser = Depends(require_organizer),
) -> OrganizerProfile:
    result = await db.execute(
        select(OrganizerProfile).where(
            OrganizerProfile.user_id == current_user.id
        )
    )
    profile = result.scalar_one_or_none()
    if not profile:
        raise HTTPException(status_code=404, detail="Organizer profile not found")
    return profile


async def get_market_for_organizer(
    market_id: uuid.UUID,
    db: AsyncSession = Depends(get_db),
    current_user: CurrentUser = Depends(require_organizer),
) -> Market:
    """Get a market that belongs to the current organizer."""
    result = await db.execute(
        select(Market).where(
            Market.id == market_id,
            Market.organizer_id == current_user.id,
        )
    )
    market = result.scalar_one_or_none()
    if not market:
        raise HTTPException(status_code=404, detail="Market not found")
    return market

The get_market_for_organizer dependency combines the market lookup with the ownership check. A market that exists but belongs to a different organizer returns 404, not 403. Returning 403 confirms the market exists, which leaks information. Returning 404 is ambiguous: the market either does not exist or the user cannot access it.

Using the Dependency in Routers

# backend/app/routers/applications.py
from fastapi import APIRouter, Depends
from sqlalchemy import select
from sqlalchemy.ext.asyncio import AsyncSession

from app.database import get_db
from app.dependencies import get_market_for_organizer
from app.models.application import Application
from app.models.market import Market
from app.schemas.application import ApplicationRead, ApplicationStatusUpdate

router = APIRouter(prefix="/api/markets/{market_id}/applications", tags=["applications"])


@router.get("/", response_model=list[ApplicationRead])
async def list_applications(
    db: AsyncSession = Depends(get_db),
    market: Market = Depends(get_market_for_organizer),
) -> list[ApplicationRead]:
    # market is already verified to belong to the current organizer
    result = await db.execute(
        select(Application)
        .where(Application.market_id == market.id)
        .order_by(Application.created_at.desc())
    )
    applications = result.scalars().all()
    return [ApplicationRead.model_validate(a) for a in applications]


@router.patch("/{application_id}", response_model=ApplicationRead)
async def update_application_status(
    application_id: uuid.UUID,
    body: ApplicationStatusUpdate,
    db: AsyncSession = Depends(get_db),
    market: Market = Depends(get_market_for_organizer),
) -> ApplicationRead:
    result = await db.execute(
        select(Application).where(
            Application.id == application_id,
            Application.market_id == market.id,  # Scoped to this market
        )
    )
    application = result.scalar_one_or_none()
    if not application:
        raise HTTPException(status_code=404, detail="Application not found")

    application.status = body.status
    await db.commit()
    await db.refresh(application)
    return ApplicationRead.model_validate(application)

Every query that accesses market data filters by market.id, which is already verified to belong to the current organizer. The developer cannot accidentally query all applications across all markets because the market object is injected through the dependency.

Vendor Data Access

# backend/app/dependencies.py (additions)
async def get_vendor_profile(
    db: AsyncSession = Depends(get_db),
    current_user: CurrentUser = Depends(require_vendor),
) -> Vendor:
    result = await db.execute(
        select(Vendor).where(Vendor.user_id == current_user.id)
    )
    vendor = result.scalar_one_or_none()
    if not vendor:
        raise HTTPException(status_code=404, detail="Vendor profile not found")
    return vendor

# backend/app/routers/vendor_portal.py
@router.get("/my-applications", response_model=list[ApplicationRead])
async def my_applications(
    db: AsyncSession = Depends(get_db),
    vendor: Vendor = Depends(get_vendor_profile),
) -> list[ApplicationRead]:
    result = await db.execute(
        select(Application)
        .where(Application.vendor_id == vendor.id)
        .order_by(Application.created_at.desc())
    )
    return [ApplicationRead.model_validate(a) for a in result.scalars().all()]

Vendors see only their own applications. The query filters by vendor.id, which is derived from the authenticated user’s JWT. A vendor cannot pass a different vendor ID in the request because the ID comes from the dependency, not the URL.

The Trap

# TRAP: Market ID from URL without ownership check
@router.get("/api/markets/{market_id}/applications")
async def list_applications(
    market_id: uuid.UUID,
    db: AsyncSession = Depends(get_db),
    current_user: CurrentUser = Depends(require_organizer),
):
    # Any organizer can see any market's applications
    # by guessing or enumerating market_id values
    result = await db.execute(
        select(Application).where(Application.market_id == market_id)
    )
    return result.scalars().all()

# SAFE: Market accessed through the ownership-checking dependency
@router.get("/api/markets/{market_id}/applications")
async def list_applications(
    db: AsyncSession = Depends(get_db),
    market: Market = Depends(get_market_for_organizer),
):
    result = await db.execute(
        select(Application).where(Application.market_id == market.id)
    )
    return result.scalars().all()

The trap version uses market_id directly from the URL. Any authenticated organizer can see any market’s applications by guessing UUIDs. The safe version uses the get_market_for_organizer dependency, which returns 404 if the market does not belong to the current user.

The Cost

The dependency injection pattern adds one additional database query per request (looking up the organizer profile or market ownership). This is approximately 1-3ms per request. At Marketflow’s scale, this overhead is invisible. If profiling (Chapter 11) shows it is significant, the organizer profile can be cached in Redis for the duration of the session.