The data isolation audit: every endpoint must be scoped to the requesting user

The bug that's easy to miss in review

You're building a multi-tenant application. A user can see their own data, not anyone else's.

You add a new endpoint:

@router.get("/api/searches")
async def get_searches(db = Depends(get_db)):
    return db.query("SELECT * FROM searches WHERE active = 1")

You test it. It returns your searches. Works correctly, for you, because you're the only user in development.

In production, with multiple users, it returns everyone's searches to everyone. The user_id filter is missing. You have a data isolation breach.

Why this happens

Data isolation failures are rarely intentional. They happen because:

1. The developer tested with a single user account and didn't observe the failure
2. The filter was present in some query functions and assumed in others
3. A refactor removed the filter accidentally
4. The endpoint was copied from a public-data endpoint and the filter wasn't added

Code review catches many bugs, but data isolation failures are hard to spot unless the reviewer is specifically looking for missing user_id clauses on every query.

The systematic audit

Before shipping any feature, audit every endpoint:

For each endpoint that returns data:
  □ Does the query include WHERE user_id = current_user.id?
  □ OR is this data intentionally public? (document why)
  □ OR is this an admin endpoint? (require admin role check, not just auth)

For admin bypass, the filter must be explicit, not omitted:

# Wrong: admin bypass by omitting the filter
if user.is_admin:
    return db.query("SELECT * FROM searches")
else:
    return db.query("SELECT * FROM searches WHERE user_id = ?", [user.id])

# Right: explicit bypass with documentation
if user.is_admin:
    # Admin can see all searches for support purposes
    return db.query("SELECT * FROM searches ORDER BY created_at DESC")

The distinction matters for auditing. An omitted filter is invisible. An explicit # Admin can see all comment is visible and intentional.

Writing negative tests

For each scoped endpoint, write a test that verifies cross-user access is blocked:

def test_user_cannot_see_other_users_searches(client, user_a, user_b):
    # user_b creates a search
    client.post("/api/searches", json={"keyword": "test"}, headers=auth(user_b))
    
    # user_a fetches searches, should not see user_b's search
    res = client.get("/api/searches", headers=auth(user_a))
    slugs = [s["keyword"] for s in res.json()]
    assert "test" not in slugs

This test fails immediately if the user_id filter is missing. It cannot be accidentally removed during a refactor without breaking the test.

Geolocation and sensitive fields: negative tests for field presence

Some data isolation isn't about users, it's about which fields should never appear in any response:

def test_api_response_contains_no_location_data(client, user):
    res = client.get("/api/products", headers=auth(user))
    for product in res.json():
        assert "location_lat" not in product
        assert "location_lng" not in product
        assert "seller_address" not in product

These negative tests verify that sensitive fields are absent from the response. They're easy to write and catch the "I added a new field to the model and forgot to exclude it from the serialiser" class of bug.

The rule

Every query that touches user data has user_id = current_user.id in its WHERE clause, or a documented, tested reason why it doesn't. There is no middle ground.